Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
quriouss
Creator III
Creator III

Better to create derived Measures during Load, or after Load (in Sense App?)

(With thanks to Michael Tarallo for correcting my posting)

My data Structure is like this;

CompanySales
Walmart1,400,000
Target1,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,

1 Solution

Accepted Solutions
JonnyPoole
Employee
Employee

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

CompanySalesWalmart Sales

Target Sales

Walmart140000014000000
Target160000001600000

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

View solution in original post

3 Replies
JonnyPoole
Employee
Employee

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

CompanySalesWalmart Sales

Target Sales

Walmart140000014000000
Target160000001600000

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

ramoncova06
Specialist III
Specialist III

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.

quriouss
Creator III
Creator III
Author

Thanks - I'll give it a try in the load script.