Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

ken_flynt
New Contributor

Need help with Table Totals on group by

In a simple table, what formula could be used to calculate the "Pct" column and correctly display both the Total Pct and each row's Pct ?

1. All Data:

Total:                                        100% (numerator/denom - 25/25)

col1   col2   numerator   denom   Pct

A       Red        2            10        20%

A       Yellow    3             10       30%

A       Green     5             10       50%

B       Red        4             10       40%

B       Greed      6            10       60%

C      Red         4            5         80%

C      Yellow      1            5         20%

2. Click on / filter on "Red"

Total:                                        40%  (numerator/denom - 10/25)

col1   col2   numerator   denom   Pct

A       Red        2            10        20%

B       Red        4             10       40%

C      Red         4            5         80%

In sql, this would be a "sum(distinct denom) group by col1" for the denominator of Pct - simple enough, However, not sure how to make this work in the Qlik Sense Table.  Would greatly appreciate anyone who is able to help. Thanks in advance!

3 Replies
rupamjyotidas
Valued Contributor

Re: Need help with Table Totals on group by

For %

You may need to group it

sum(distinct denom)/sum(TOTAL <yourDimensions>distinct denom)

Re: Need help with Table Totals on group by

May be like this:

=Sum(numerator)/Sum({1} TOTAL <col1> numerator)

Capture.PNG

millan123
Contributor II

Re: Need help with Table Totals on group by

You can use like

Sum(numerator)/Sum({1< TOTAL ='col1'>} numerator)