topic Totals in Pivot table in QlikView

Totals in Pivot table

Abhishek1 — Thu, 02 May 2019 11:48:49 GMT

hi,

I am developing a dashboard in which a Pivot chart needs to show Totals. The Chart shows the count of Projects and the annual revenue by Phase and by month. We are showing this data for the latest two months Separately in chart. Please see the attached image for better understanding.

The issue is The users want the Totals to show separately for every month in the end. just like the period is divided in every phase.

can anyone help?

Re: Totals in Pivot table

sunny_talwar — Thu, 02 May 2019 12:44:38 GMT

Did you try to add the partial sum for Period?

Re: Totals in Pivot table

Abhishek1 — Thu, 02 May 2019 13:58:42 GMT

Hi Sunny,

Yes I did. It shows partial sum for every Phase separately. I dont want to show it like that. I want to show the Total in the end to show totals separately for each month. Please find the images of the output after applying partial sum and what I am trying to achieve. Please see the Total section of the Desired_Output image.

Re: Totals in Pivot table

sunny_talwar — Thu, 02 May 2019 14:01:58 GMT

Try like this... create an island table in the script like this

LOAD * INLINE [
Dim
1
2
];

and then change Phase dimension to this
Pick(Dim, Phase, 'Total')

Sort order for this new dimension will use sort by expression
Only({1} Dim)*10000000 + any other sorting expression you have

Re: Totals in Pivot table

Abhishek1 — Mon, 06 May 2019 06:49:55 GMT

hi,

I understood that I have to create a data island named Dim. I am not clear what I have to do next. can you please explain a bit. sorry I am new here.

Thanks,

Abhishek

Re: Totals in Pivot table

Abhishek1 — Mon, 06 May 2019 06:52:39 GMT

hi Sunny,

I understood that I have to create a data island named Dim. I am not clear what I have to do next. can you please explain a bit. sorry I am new here.

Thanks

Re: Totals in Pivot table

sunny_talwar — Mon, 06 May 2019 11:55:41 GMT

Are you able to share a sample where you trying to do this? It might be easier for you to see it implemented

Re: Totals in Pivot table

jaibau1993 — Mon, 06 May 2019 12:25:12 GMT

Hi both!

I made a simplified example to ilustrate Sunny's solution (it is fanstastic, by the way, so clever).

Please see the attached file, maybe it helps you understand Sunny.

Jaime

Re: Totals in Pivot table

Abhishek1 — Mon, 06 May 2019 13:39:18 GMT

Brilliant, Thanks Very much.