While we have used the search strings in previous recipes to do numeric search, we can also do text searches by using the wild card character *. However, sometimes you might want to compare the values in one field to the values stored in another. We can also achieve this using Set Analysis and the concat() function.
For the purpose of this recipe, we make use product sales data as defined in the following script. Load the following script into the data load editor:
Transactions:
Load *,
If(Len(TmpSubCategory)=0,Null(),TmpSubCategory) AS SubCategory;
Load * INLINE [
ProductType, Category, TmpSubCategory, Sales
Premium,A4,A4,300
Standard,A4,A4,100
Premium,A5,A5,500
Standard,A5,A5,200
Premium,A6,A6,1000
Standard,A6,A6,600
Premium,A1,,700
Standard,A1,,300
Premium,A2,,300
Premium,A3,,200
Standard,A3,,60
]; - Drag a table object onto the content area and label it as
Product Sales. - Add Product Type as a dimension
- Add the following expression as the first measure and label it as
Total Sales:Sum (Sales)
- Add the following expression as the second measure and label it as
Sub Category Sales:Sum ({<Category = {$(=concat (distinct [SubCategory], ','))} >} Sales) - You should have a table that looks similar to the following image:
The concat() function wraps around a field name; when expressed it lists every field value separated by a delimiter. As such, the function concat (Distinct Subcategory,',') returns A4, A5, A6, which are all the values in the sub-category field with no selections made.
Using the concat() function means you can avoid having to write out large lists of text strings in your Set Analysis expression. Even better, if these lists come from a source system where they are automatically updated with data.