Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

quriouss
Contributor 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
Employee
Employee

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

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

3 Replies
Employee
Employee

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

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
Valued Contributor III

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

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
Contributor III

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

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