Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
william_fu
Creator II
Creator II

Referencing Measures in other Measures

Hello,

I'm very new to Qlik Sense (coming from a DAX background) and I keep trying to reference master item measures in other measures like I did in Powerpivot.

Can they not be reused? I'm rebuilding reports from scratch in Qlik Sense and I have a ton of measures that are derivative from others.

I've seen something on creating variables and referencing them as $(var).


While we're on the topic, are there any resources explaining the proper syntax for Qlik Sense expressions? I'm having trouble with all the $<{ symbols and the "Error in expression" flag within the editor doesn't really help.

Currently on the September 2017 version.


Thanks!

1 Solution

Accepted Solutions
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi WIlliam,

to summarize what others mentioned here:

- Unfortunately, Master Items cannot be referenced directly in other Master Items. This would be a nice feature, but it's not currently there.

- What most professionals do to overcome this problem, is store various formulas in variables and then use the variable in the definition of the Master Items. Multiple variables can be combined in expressions. For example:

Variables:

      exp_Sales = 'sum(Sales)'

      exp_Cost = 'sum(Cost)'

Master Item Definitions:

    Total Sales = $(exp_Sales)

    Total Cost = $(exp_Cost)

    Margin % = ($(exp_Sales) - $(exp_Cost)) /$(exp_Cost)


-  While you may manage your variables manually, using the little variable overview "x=", it's more robust if you store your formulas in an external text file and load them during the data load, generating the variables during the load. This way, you can share these definitions across multiple apps, and maintain them in one central place.

- The syntax that involves {$< ... >} is called Set Analysis. Unfortunately, the expression editor doesn't offer much help for troubleshooting the syntax, other than telling you that something must be wrong (and sometimes even that is a false alarm...). You kinda have to know the syntax and find any errors manually. There are many sources, free or commercial, that describe Set Analysis. I'm teaching a half-day lecture about it at the Masters Summit for Qlik.

I'm describing all of these techniques, including Set Analysis and many other advanced Qlik development techniques in my book QlikView Your Business: The Expert Guide to QlikView and Qlik Sense. Check it out!

cheers,

Oleg Troyansky 

View solution in original post

8 Replies
rittermd
Master
Master

There is plenty of documentation on Set Analysis and probably a bunch of YouTube videos explaining the syntax.

Typically you can't reference a master measure(a saved measure)  inside of another measure.  You can reference variables inside of a measure.  But whether this works or not really depends on the specifics of what you are trying to do.

cazztfplease
Creator
Creator

You can define variables here that can be referenced throughout your chart objects and script.

Comm.PNG

jananireddy
Partner - Creator
Partner - Creator

Hi William,

May be you can use the column function in Qlik sense

Pasting a discussion link here Column() function

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi WIlliam,

to summarize what others mentioned here:

- Unfortunately, Master Items cannot be referenced directly in other Master Items. This would be a nice feature, but it's not currently there.

- What most professionals do to overcome this problem, is store various formulas in variables and then use the variable in the definition of the Master Items. Multiple variables can be combined in expressions. For example:

Variables:

      exp_Sales = 'sum(Sales)'

      exp_Cost = 'sum(Cost)'

Master Item Definitions:

    Total Sales = $(exp_Sales)

    Total Cost = $(exp_Cost)

    Margin % = ($(exp_Sales) - $(exp_Cost)) /$(exp_Cost)


-  While you may manage your variables manually, using the little variable overview "x=", it's more robust if you store your formulas in an external text file and load them during the data load, generating the variables during the load. This way, you can share these definitions across multiple apps, and maintain them in one central place.

- The syntax that involves {$< ... >} is called Set Analysis. Unfortunately, the expression editor doesn't offer much help for troubleshooting the syntax, other than telling you that something must be wrong (and sometimes even that is a false alarm...). You kinda have to know the syntax and find any errors manually. There are many sources, free or commercial, that describe Set Analysis. I'm teaching a half-day lecture about it at the Masters Summit for Qlik.

I'm describing all of these techniques, including Set Analysis and many other advanced Qlik development techniques in my book QlikView Your Business: The Expert Guide to QlikView and Qlik Sense. Check it out!

cheers,

Oleg Troyansky 

william_fu
Creator II
Creator II
Author

Thank you for the thorough response and the txt file tip Oleg!

Is it possible to add filters for these variables, or is each one its own "entity"?

For example, I currently have these two variables:

TTD = Count({$<DtInclusao-={'1/1/1900'}>} NroProposta)

Booked = Count({$<IndDesemb={'S'},DtInclusao-={'1/1/1900'}>} NroProposta)

Instead of using $(Booked) as a master item, can I somehow add the IndDesemb={'S'} criteria to the $(TTD) variable?

rafaeljmvicente
Contributor II
Contributor II

Unfortunately, this approach does not work with more complex formulas: e.g.: exp_Ownership = 'if(left([Level of ownership],1)='0', 'FO', ....'  as the logic will break on the first "'" character.

I know that I can solve this data transformation via the loading script, but if you have if's with strings. It would be great to reference Master Items directly instead.

Cheers,

RV

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

Rafael,

I completely agree with you - it would be fantastic to be able to operate with Master Items within Measures and combine multiple Master Items together. I just have no way of making it happen, since I'm nowhere close to the R&D department. I can only suggest what currently works and what doesn't...

cheers,

Oleg Troyansky

davedecastro
Contributor
Contributor

Hi William,

try this set expression builder. Please note that this is in Beta only

http://tools.qlikblog.at/SetAnalysisWizard/QlikView-SetAnalysis_Wizard_and_Generator.aspx?sa=

Cheers

Dave