Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
Apologies if this is a basic question - I am new to Qlik!
I am having trouble with a ratio of two totals in QlikView.
- The total in A is just SUM(Field1).
- The total in B is just SUM(Field2)
- The ratio should be the sum of A divided by the sum of B, or 0.374
- Instead, the ratio is displaying as .416
- How can I get .374 to display in the total column?
Hello,
Used formulas are in table header.
See my output below:
Thanks so much for taking a look at this. If it worked like it is working for you, that would be wonderful! How do I get Qlik to do this? Is it a setting, or do I need to use AGGR expressions or something?
Here is another example of the output I get from Qlik. The [A]/[B] expression is correct for everything except the Total. The Total is where I need it to be correct. I need it to take a straight sum of A and divide it by a straight sum of B. It is giving me 100.6 when the correct answer is 82.6.
Group | A | B | [A]/[B] | Correct | |
705,901,957.5 | 854,638,973.1 | 1.006 | 0.825965 | ||
A | 339,898,625.1 | 455,638,791.8 | 0.746 | 0.745983 | |
B | 103,553,242.0 | 167,508,204.6 | 0.618 | 0.618198 | |
C | 31,839,473.4 | 86,839,151.2 | 0.367 | 0.366649 | |
D | 716,750.2 | 1,431,467.2 | 0.501 | 0.50071 | |
E | 76,785,342.9 | 80,865,892.2 | 0.950 | 0.949539 | |
F | 24.9 | 912.6 | 0.027 | 0.027251 | |
G | 153,108,499.0 | 62,354,553.4 | 2.455 | 2.45545 |
Can you share used formulas?
As far as no simple sum() or count() are used else any kinds of rates are calculated and/or any conditions are included you may need an aggr() to get the wanted results, for example:
avg(aggr(sum(X)/sum(Y), DimX, DimY))
which returns the average of the row-results. But against your example data it won't return 0.82 else 0.81 which just showed that there is a different weight between the row-values. Beside this could any data-anomalies have an impact - means any kind of missing values or NULL within the data respectively the associations of them.
Before going further at this point take a look on the calculation-mode within the object-properties and try the different options. Further take also a look on your data-model if there are any circular loops or synthetic keys which mean that the data-model is invalide.
Try this
SUM(Aggr(SUM(A)/SUM(B), Group))
Thanks so much to everyone for looking at this.
The formulas are:
sum(Claim_Paid) * sum(ELIG_PART_MBR_MONTH_CNT) for A
and
sum(RECVD_GROSS_REV_AMT) * sum(ELIG_PART_MBR_MONTH_CNT)
for B.
The totals work properly for each group A, B, C, etc., but when they are aggregated they aren't correct
Group | A | B | [A/B] | With Aggr |
317,033,343.3 | 846,818,258.8 | 0.416 | 233.0% | |
A | 173,272,011.8 | 455,638,791.8 | 0.380 | 38.0% |
B | 48,851,200.2 | 167,508,204.6 | 0.292 | 29.7% |
C | 17,769,545.9 | 86,839,151.2 | 0.205 | 19.3% |
D | 676,037.3 | 1,431,467.2 | 0.472 | 45.4% |
E | 27,521,443.2 | 73,045,177.9 | 0.377 | 38.0% |
F | 0.0 | 912.6 | 0.000 | 0.0% |
G | 48,943,104.8 | 62,354,553.4 | 0.785 | 62.6% |
should be | 0.374381799 |