Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
March 11, 2PM EST: Do More with Qlik - Getting Started wtih Qlik Sense SaaS - Redux. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
johnkoegel
Contributor
Contributor

Pivot Table Measure Display Only at Line Item Level

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

2 Replies
ramoncova06
Specialist III
Specialist III

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

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