Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
For %
You may need to group it
sum(distinct denom)/sum(TOTAL <yourDimensions>distinct denom)
May be like this:
=Sum(numerator)/Sum({1} TOTAL <col1> numerator)
You can use like
Sum(numerator)/Sum({1< TOTAL ='col1'>} numerator)