Solved: Percentages not adding up correctly - Qlik Community

Report Inappropriate Content · ‎2011-02-22

I'm having issues getting my chart to produce the correct results. I'm sure it's my expression formula but I've tried everything I could find on the other posts and nothing is working.

I have stores that get inspected a couple of times each year. They are given a score. I need to show the scores grouped by quarter and then the percentage of stores in that group/quarter. The %'s aren't adding up to 100 for each quarter....

Here is the current expression: Count(ACCOUNT)/Count(TOTAL <Quarter> ACCOUNT)

I get the same results without the <Quarter>, so I'm thinking something wrong...???

I have two dimensions, Score & Quarter. Score =If(TOTAL_SCORE > 0.899, '90% and above', if(TOTAL_SCORE < 0.9 AND TOTAL_SCORE > 0.799, '80% - 89.9%', if(TOTAL_SCORE < 0.8, '79.9 % and below'))) Quarter =QuarterName(Date)

Thanks in advance for any help!

johnw · ‎2011-02-23

The attached example shows a total of 100% for each Quarter in the top chart, and a total of 100% for each ScoreRange in the bottom chart. Fields are established in script. I used a dual() function because I prefer numeric sorting for numeric-like data.

View solution in original post

Report Inappropriate Content · ‎2011-02-22

Hi Sturev,

I am confused with the TOTAL statement as well each time I use it. Not sure if I understand your requirement correctly, but I believe using TOTAL <Score> should work out for you

cheers

Florian

Report Inappropriate Content · ‎2011-02-22

try the stacked bar chart for better checking/validation of the claim.

Report Inappropriate Content · ‎2011-02-22

When I change it to Score it says "No data to display", so that didn't work... but thanks for the suggestion! I'm learning too!

Report Inappropriate Content · ‎2011-02-22

I have a simple count chart so I can double check the %'s...

=Count(ACCOUNT)

Report Inappropriate Content · ‎2011-02-22

If I switch it to a piviot table here's what it looks like...

Report Inappropriate Content · ‎2011-02-22

After reading other posts, I'm wondering if the calculated dimensions have to be done in the load script???

johnw · ‎2011-02-22

Score is a calculated dimension? You might need to do that in the script, yes. I'm not sure an aggr() can refer to a field that doesn't exist. And if Quarter is a calculated dimension, that should definitely be in the script, even if I don't think you need that for this particular problem.

Report Inappropriate Content · ‎2011-02-22

Thanks John! Yes, both the Score and the Quarter are Dimensions... So my logic for the score is an if statement, would I just put that in the load script and put AS TotalScore? If so, would i just put field TotalScore in the dimension, or would that change things all around? Sorry, I'm a novice here... 😞

You mention not needing quarter? I'm confused on that one as I need the chart to be by quarter???

johnw · ‎2011-02-23

Yes, you'd move the if() for total score into the script. I'd probably call it "Total Score Group" or something to distinguish it from your TOTAL_SCORE field. Then yes, you'd use "Total Score Group" as your first dimension instead of the if().

I didn't mean that you didn't need quarter at all. I just meant that solving your problem does not require you to move that calculation to the script, only the score calculation. But I do recommend that you calculate quarter in the script instead of the chart so that you only calculate it once instead of every time anyone looks at the chart. It might not matter if your data set is small, but if your data set is large it might speed the chart up a little.