Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
mrutyunjaya
Contributor III
Contributor III

Qlik Sense Pivot table - Calculating percentages

Hi,

I am trying to convert Values in a Pivot table into Percentages. 


The expression used to get the counts / values is:

>     Count(distinct ID)

I tried using the below formula to get the percentages, but the effort is in vain.

>    Count(distinct ID) / Count( total distinct ID)

>    Count(distinct ID) / Count({$<Date1>}  total distinct ID))

>    Count(distinct ID) / Count({$<Date2>}  total distinct ID))

>    Count(distinct ID) / Count({$<Date1>}  distinct ID))

>    Count(distinct ID) / Count({$<Date1>} distinct ID))


The sample data appears like this:

Table 1:

Date2
Date12016May2016Jun2016Jul2016Aug
Totals1570200330560380
2016May56020015090120
2016Jun430-18022030
2016Jul330--25080
2016Aug250---150

Table 2: Required

Date2
Date12016May2016Jun2016Jul2016Aug
Totals1570
2016May56036%27%16%21%
2016Jun430-42%51%7%
2016Jul330--76%24%
2016Aug250---60%

Kindly suggest the appropriate method to get the results.

Regards,

Mrutyunjaya

5 Replies
Gysbert_Wassenaar

Try Count(distinct ID) / Count(TOTAL <Date1> distinct ID)


talk is cheap, supply exceeds demand
mrutyunjaya
Contributor III
Contributor III
Author

Hi Gysbert,

The objective is not met here again. The formula divides each value by Total(row totals).

viz; 560 / 1570

       200 / 1570

       150 / 1570

But, the desired value is,

viz;  560 / 560

       200 / 560

       150 / 560

Reg,

Mrutyunjaya

sadasiva
Creator II
Creator II

How about removing the show total under dimension options and using the same formula

mrutyunjaya
Contributor III
Contributor III
Author

No changes. The result is same.

Suki
Contributor
Contributor

Hi Mrutyunjaya,

Could you please let me know if the scenario of calculating % has been achieved in QS pivot table? I am working on the same scenario, but still couldn't crack the solution to it.

Thanks in advance.