Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
(With thanks to Michael Tarallo for correcting my posting)
My data Structure is like this;
Company | Sales |
---|---|
Walmart | 1,400,000 |
Target | 1,600,000 |
But I want to create two measures; "Walmart Sales" and "Target Sales".
I can create two derived Metrics as 'Master Items';
SUM (If (Company = "Walmart" , "Sales")) to give Walmart Sales
SUM (If (Company = "Target" , "Sales")) to give Target Sales
An I can do this inside my QlikSense app. But is this the best place to do it, or should I be doing it within by data load script? Is there any difference to the usability (apart from them being created as 'Master Items')?
Thanks,
In the load script you would have 2 options:
Use IF statements to extend the table to something like this.
-> the main issue with this is that a user would STILL need to add sum(Walmart Sales) or avg(Walmart Sales) to see the sums in the UI. This is needed to see the summarized totals for anything but the granular data
Company | Sales | Walmart Sales | Target Sales |
---|---|---|---|
Walmart | 1400000 | 1400000 | 0 |
Target | 1600000 | 0 | 1600000 |
The other way to do this in the load script is to use a variable:
set vWalmartSales='sum( if(Company='Walmart',Sales)) ';
you could also do this which i would recommend. Its an alternative syntax that uses SET ANALYSIS which can be faster
set vWalmartSales='sum( {<Company={Walmart}>} Sales) '
In this scenario the 2nd is pretty useful because you can use it in ANY place expressions are allowed... 'color by expression' , 'measure expression', 'dimension expresison' even in a master item.
If you do it as a Master Item, the key benefit is that your users can quickly drag and drop it to create new visualizations using the same master expression. Thats a good thing. Only issue with this (and only reason why you may not use this) is that master items can't be referenced by OTHER expressions just yet. They will soon but not right now. Variables can.
So variables may be the most flexible because you can use them everywhere, but now or perhaps soon doing it in one place (Master Items) may feel cleaner and allow administrators to rely less on script.
hope it helps
In the load script you would have 2 options:
Use IF statements to extend the table to something like this.
-> the main issue with this is that a user would STILL need to add sum(Walmart Sales) or avg(Walmart Sales) to see the sums in the UI. This is needed to see the summarized totals for anything but the granular data
Company | Sales | Walmart Sales | Target Sales |
---|---|---|---|
Walmart | 1400000 | 1400000 | 0 |
Target | 1600000 | 0 | 1600000 |
The other way to do this in the load script is to use a variable:
set vWalmartSales='sum( if(Company='Walmart',Sales)) ';
you could also do this which i would recommend. Its an alternative syntax that uses SET ANALYSIS which can be faster
set vWalmartSales='sum( {<Company={Walmart}>} Sales) '
In this scenario the 2nd is pretty useful because you can use it in ANY place expressions are allowed... 'color by expression' , 'measure expression', 'dimension expresison' even in a master item.
If you do it as a Master Item, the key benefit is that your users can quickly drag and drop it to create new visualizations using the same master expression. Thats a good thing. Only issue with this (and only reason why you may not use this) is that master items can't be referenced by OTHER expressions just yet. They will soon but not right now. Variables can.
So variables may be the most flexible because you can use them everywhere, but now or perhaps soon doing it in one place (Master Items) may feel cleaner and allow administrators to rely less on script.
hope it helps
I believe Jonathan already explained on how to do this either way
I would just ask which do you prefer having a shorter ETL or presenting the data faster ?
Normally is considered as a best practice to do whatever you can on the backend script and then just leave simple calculations for the front end, this way the performance is not issue when users apply different filters.
Thanks - I'll give it a try in the load script.