Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I am very new to Qlik Sense, and I am wondering whether there is a way to have a dynamically calculated Measure display at only the line item level of a Pivot Table. For example, can I calculate a range [max(ItemPrice) - min(ItemPrice)] only at the deepest aggregation level? Right now it simply displays at every level of aggregation, and at the highest level (Company) I see ranges of thousands of dollars when the actual line item ranges are very small.
As a possible workaround, is there a way to apply to a background color expression to the deepest aggregation level only?
Thank you in advance for the help.
John
You can define what level you want to show, though it depends on what dimensions you have in the pivot table
you can do this either by using total, this depends on the dimensions or aggr which creates a temp table
https://community.qlik.com/docs/DOC-3857 // how to use aggr
https://community.qlik.com/blogs/qlikviewdesignblog/2013/09/09/aggregation-scope // how to use total
You can do that with a conditional expression in your measure.
Lets say you have a pivot table with 2 dimensions on the rows and 1 dimension on the columns.
If the highest level (1st) is company and the 2nd level is employee than you can change the measure for the 1st and 2nd dimension using the dimensionality() function:
In this case Dimensionality()=1 refers to cells calculated at the Company level and Dimesionality()=2 refers to cells calculated at the Employee level
So your measure can be
if( Dimensionality()=2 , <expression for employees>, <expression for all other levels including grand totals> )