topic Re: Issue with Pivot table in Qlik Sense in App Development

Issue with Pivot table in Qlik Sense

mansi_dv — Mon, 20 Dec 2021 21:08:46 GMT

Hi All,

I am building a pivot table in Qliksense for zonewise number of registrations and target registrations for a bank. In dimensions, I have a zone column and a date column. For measure, I have new number of registrations. So the data shown is new registrations per day per zone.

My client is basically interested in the total column that comes up at the beginning. So it shows sum of zone wise registrations for all the dates, depicted in image 1.

Now the new metric that I want to add is, target registrations which is calculated as:
5*count(distinct branch_id)

As the grain of both the metric calculations is not same, I get the same target for all dates and the total column which is not correct(illustrated in image 2).
My client is only interested in the totals section and does not want to see the individual day wise target(as the value is wrong). This is illustrated in image 3. For this, I removed the 2nd expression and tried adding it as a calculated dimension but it did not work.

Can someone help in meeting this requirement?

Thanks

Re: Issue with Pivot table in Qlik Sense

Or — Fri, 15 May 2020 19:48:42 GMT

I'm fairly sure you were headed in the right direction when you tried creating a calculated dimension, but you probably need to wrap it in an AGGR() to make it work:

5*Aggr(count(distinct branch_id),Zone)

Re: Issue with Pivot table in Qlik Sense

Gui_Approbato — Fri, 15 May 2020 22:57:26 GMT

Hello,

I think you can work this out with the content from this post:

https://community.qlik.com/t5/New-to-QlikView/Pivot-table-Hide-measure-column-based-on-dimension-value/m-p/1246791

I have never tried but it appears to be somewhat similar.