Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

pauledrich
Contributor

Pivot Table - Calculate Percentages

Hi and thank you in advance..

I have a Pivot table which is calculated from the load script with a cumulative last day of the month value Flag -  I want to calculate % +or-.

EG...

Regions                     Sales 1     Sales 2     % +/-

Region 1                    100          90                    ?

Region 2                    200          210                   ?    

Region 3                    150          131                    ?

Should this be done in the load script (If so how)?

Or

Better as an expression calculation!

I have explored Column (1) / Column (2) - with no result.

4 Replies
laujerry
Contributor

Re: Pivot Table - Calculate Percentages

sorry, what is ur expected result?

Not applicable

Re: Pivot Table - Calculate Percentages

if Sale2 and Sale1 are your fields,

then try this

(Sale2 - Sale1)/Sale1

or try this

(sum(Sale2) - sum(Sale1))/sum(Sale1)

*******************************************

and in number tab check the percent option

er_mohit
Honored Contributor II

Re: Pivot Table - Calculate Percentages

make sure your sale 1 and sale 2 is enclosed with sum() function like this

sum(Sale1)

and same for

sum(Sale2)

so for third expression column(2)/Column(1)  which you want and for this in number tab for this column fixed to 2 and check on show in %

hope it helps if you want variance % then try this

(column(2)-column(1))/Column(1)

Not applicable

Re: Pivot Table - Calculate Percentages

Num((sum(Sale2) - sum(Sale1))/sum(Sale1),'#,##0% ')

or

(sum(Sale2) - sum(Sale1))/sum(Sale1)

& in number tab check the percent option

Community Browser