Pivot Table Measure Display Only at Line Item Level

[Anonymous]

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

[ramoncova06]

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

[JonnyPoole]

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>  )