topic Proportion at Subtotal Level in QlikView

Proportion at Subtotal Level

carlcimino — Wed, 24 May 2017 15:13:59 GMT

Hello, I have a Pivot Table chart object that looks like the picture below. I have three metrics that break the total by Segment. Here is the Set Analysis I have to calculate the proportion at the BDM level. My issue is that the Subtotals at the Branch/Region and overall total do not show the same sum to 100% as the BDM level. How would you accomplish that? Any help would be much appreciated.

Thanks,

Carl

sum({<YEAR={$(=max(YEAR))},SEGMENT={"Portfolio"}, [New HH Count]={1}>}[Policy Total TPREM])
/
sum(total<BDM>{<YEAR={$(=max(YEAR))}, [New HH Count]={1}>}[Policy Total TPREM])

Re: Proportion at Subtotal Level

sunny_talwar — Wed, 24 May 2017 17:20:25 GMT

I think you might have to use some form of How to use - Dimensionality() to get this working.... If you can share a sample... we might be able to have a look at this

Re: Proportion at Subtotal Level

Sergey_Shuklin — Wed, 24 May 2017 17:44:52 GMT

Hello, Carl!

One of the most painless way is simply create a calculation within the script which will make additional rows for level BDM which will contain the aggr total sum. It's some kind of hand-made total =D .. Why not.

Re: Proportion at Subtotal Level

carlcimino — Wed, 24 May 2017 18:19:51 GMT

how would I "share a sample"?

Re: Proportion at Subtotal Level

carlcimino — Wed, 24 May 2017 18:20:23 GMT

do you have an example of how to do that in the script or point to a thread that references the idea?

Re: Proportion at Subtotal Level

sunny_talwar — Wed, 24 May 2017 18:22:03 GMT

Check these links out

Preparing examples for Upload - Reduction and Data Scrambling

Uploading a Sample

Re: Proportion at Subtotal Level

Sergey_Shuklin — Wed, 24 May 2017 18:37:18 GMT

Oh I guess I've got it! Try to use "sum(total<Branch,BDM>" instead of "sum(total<BDM>".

Re: Proportion at Subtotal Level

carlcimino — Wed, 24 May 2017 20:07:55 GMT

Rock on Sergey. The below returned what I needed. Thank you.

sum({<YEAR={$(=max(YEAR))},SEGMENT={"Portfolio"}, [New HH Count]={1}>}[Policy Total TPREM])
/
sum(total<Branch, BDM, Region>{<YEAR={$(=max(YEAR))}, [New HH Count]={1}>}[Policy Total TPREM])