topic Pivot table partial sum issue in QlikView

Pivot table partial sum issue

Mon, 25 Jul 2011 17:36:14 GMT

Hello Everyone,

I have the following pivot table as shown in the attached screenshot.

The problem is i am not getting a correct value for "% of Total Value" and "% of Opportunity".

The requirement is: % of Total Value should calculate the percentage contribution of the bucket, stage and cyclic group.

If you can kindly have a look into the screenshot,

what I want in % of Total Value is a ratio?

For example for the first line it should be

2,362,381,545/2,362,381,545 = 100%

Second line should be 316,643,697/316,643,697= 100%

and the third line should be 609,040/609,040 =100%

but the 9th line should be 119,925,268/161,821,219= 74.1%

and the 15th line should be 11,160,830/11,285,790= 98.89% and so on.

And I have no clue how to do it.

I need you all experts' help.

Thanks,

erichshiino — Mon, 25 Jul 2011 18:38:07 GMT

The actual expression you should use will depend on the field (and group) names you are using.

But if the Total Value expression is: sum (Value)

The % expression will be:

=Sum(Value)/

Sum(total <GroupName, Stage, Bucket> Value) //Where GroupName should be replaced by the name of the cyclic group on your chart.

Hope this helps,

Erich

Mon, 25 Jul 2011 21:53:29 GMT

Hi Erich,

Thanks for your answer. But it seems like the expression is not

taking the cyclic groups' name. In return its gving me all zeros.

This is what I wrote in the denominator of the expression.

sum({$<

FACT_TYPE={'Current'},

FLAG_HEALTH_REPORT={'1'},

REVENUE_RECOGNITION_FLAG ={'Yes'},

IS_PART_ACTIVE={'1'},

MODIFIED_DATE={"<=$(vMaxDate)"}

total <[Group 1],STAGE, Bucket_Test> [TOTAL_VALUE])

Thanks,