topic Accumulated Percentage in Pivot Table in QlikView

Accumulated Percentage in Pivot Table

Tue, 11 Mar 2014 10:28:59 GMT

Hi All,

i am trying to create an accumulated percentage in a pivot table chart. Please take a look at the charts below :

this is the output that i am expeceting :

but when i tried it in QV it keeps showing this value (picture below), for each days it will show what is the percentage against the TOTAL.

What i am expecting is, the 2nd, 3rd, days and so on and so forth will be the accumulated with the percentage of the days before.

what i used in the Expression :

Count({$<Days={">$(=0)"}>}Nomor)

/Aggr(NODISTINCT Count(Nomor),Week)

attached is my QVW.

Really appreciate for the help guys.

Thanks in advance

MK_QSL — Tue, 11 Mar 2014 10:36:01 GMT

Change expression as below

RangeSum(Above(Count({$<Days={">$(=0)"}>}Nomor)/Aggr(NODISTINCT Count(Nomor),Week),0,RowNo()))

This function might not give you the total at the end but if you use below you will get what you want to achieve.

IF(

Dimensionality()=1,

RangeSum(Above(Count({$<Days={">$(=0)"}>}Nomor)/Aggr(NODISTINCT Count(Nomor),Week),0,RowNo())),

Aggr(Count({$<Days={">$(=0)"}>}Nomor)/Count(Nomor),Week)

)

Wed, 12 Mar 2014 02:25:24 GMT

Dear Manish,

thanks alot it works!

but why i am getting "0" as the first line? and maybe could you kindly help to elaborate more the reason why we use IF ( Dimensionality()=1) ?

MK_QSL — Wed, 12 Mar 2014 06:10:11 GMT

Dimensionality is 1 for normal rows and 0 for Total rows.

Try to add one more expression Dimensionality() to understand it.

Without this you will get the result but not the total what you want.

Hope this helps.

UPDATE : To remove 0 for first line, go to presentation tab and select suppress Zero Values...