Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have 4 columns (Month, Subject, Intensity, Score)
Month | Subject | Intensity | Score | Weighted Score |
Jan | Math | 90 | 85 | 30.00000 |
Jan | English | 70 | 92 | 25.25490 |
Jan | Physics | 95 | 75 | 27.94118 |
Total | 255 | 83.19608 | ||
Feb | Math | 92 | 89 | 31.49231 |
Feb | English | 72 | 90 | 24.92308 |
Feb | Physics | 96 | 84 | 31.01538 |
Total | 260 | 87.43077 | ||
What I am looking is for new Column called Weighted Score where the calculation is as below
Score * Intensity / Total Intensity by Each Month (Jan, Feb etc.)
i.e.,
for Jan, below are the calculations
85 * 90 / 255 = 30.00
92 * 70 / 255 = 25.255
75 * 95 / 255 = 27.941
Total Weighted Score for Jan = 83.196
for Feb, below are the calculations
89 * 92 / 260 = 31.492
90 * 72 / 260 = 24.923
84 * 96 / 260 = 31.015
Total Weighted Score for Feb = 87.430
I tried using a Qlik Sense Aggr and Total formulae as below, but did not work.
sum(Score * Intensity / aggr (sum(total Intensity), Month))
Measure:
sum(Score) * Sum(Intensity) / sum(total <Month> Aggr(sum(Intensity), Month))
Measure:
sum(Score) * Sum(Intensity) / sum(total <Month> Aggr(sum(Intensity), Month))