Pivot Table percentages based on second dimension

Anonymous — Tue, 27 Jan 2015 06:18:09 GMT

Hi,

I have a pivot table with two dimensions. The second dimension is placed horizontally. My expression is as follows:

=IF((Heading3) = 1,'',

IF(Divide3=1,

num(Sum({$<Description3={'Food Cost'}>}Total MonthlyActivity*Multiplier3)

/Sum({$<Description3={'Food'}>}Total MonthlyActivity*Multiplier3),'#,##0.00%;(#,##0.00)%')

IF(Divide3=2,

num(Sum({$<Description3={'Liquor Cost'}>}Total MonthlyActivity*Multiplier3)

/Sum({$<Description3={'Liquor'}>}Total MonthlyActivity*Multiplier3),'#,##0.00%;(#,##0.00)%')

IF(Divide3=3,

num(Sum({$<Description3={'Wages'}>}Total MonthlyActivity*Multiplier3)

/Sum({$<Description3={'Total Income'}>}Total MonthlyActivity*Multiplier3),'#,##0.00%;(#,##0.00)%')

IF(Divide3=4,

num(Sum({$<Description3={'Gross Profit'}>}Total MonthlyActivity*Multiplier3)

/Sum({$<Description3={'Total Income'}>}Total MonthlyActivity*Multiplier3),'#,##0.00%;(#,##0.00)%')

num((sum(MonthlyActivity)*Multiplier3)/1000,'#,##0;(#,##0)'))))))

I use TOTAL to establish correct % in the report layout. See attached images. The single dimension report works OK. (see attached) My issue is when I attempt with second dimension the % shown are for the total values than what is for the second dimension value which is placed as a column. How can I get around to achieve the correct percentages by column. i.e. (Refer Second Dimension Image) Food Cost/Income on first column should be 22/58 = 37.9% rather than showing 33.11% for all columns.

Thanks in advance

topic Pivot Table percentages based on second dimension in QlikView

Pivot Table percentages based on second dimension