Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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.
You can define variables here that can be referenced throughout your chart objects and script.
Hi William,
May be you can use the column function in Qlik sense
Pasting a discussion link here Column() function
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
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?
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
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
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
Luckily, now it's possible, you can write (having [Sales] and [Cost] as predefined measures)
Margin = ([Sales] - [Cost]) / [Sales]