topic Percentages not adding up correctly in QlikView

Percentages not adding up correctly

Tue, 22 Feb 2011 15:44:08 GMT

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!

AW:Percentages not adding up correctly

Tue, 22 Feb 2011 15:46:19 GMT

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

Percentages not adding up correctly

Tue, 22 Feb 2011 15:48:04 GMT

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

AW:Percentages not adding up correctly

Tue, 22 Feb 2011 15:55:18 GMT

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!

Percentages not adding up correctly

Tue, 22 Feb 2011 15:58:36 GMT

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

=Count(ACCOUNT)

Percentages not adding up correctly

Tue, 22 Feb 2011 16:16:11 GMT

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

Percentages not adding up correctly

Tue, 22 Feb 2011 19:17:12 GMT

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

Percentages not adding up correctly

johnw — Wed, 23 Feb 2011 00:35:41 GMT

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.

Percentages not adding up correctly

Wed, 23 Feb 2011 01:39:49 GMT

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???

Percentages not adding up correctly

johnw — Wed, 23 Feb 2011 18:38:41 GMT

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.

Percentages not adding up correctly

Wed, 23 Feb 2011 20:49:26 GMT

Thanks for the clarification John. I moved the formulas for both dimensions into the load script and made the adjustments on the chart, but now I'm seeing 100% across all scores/quarters...???

Count(RORID)/Count(TOTAL <RORQuarter> RORID)

Percentages not adding up correctly

Wed, 23 Feb 2011 20:57:15 GMT

After checking further, it appears I have an issue with the way I put the QuarterName in the load script... Is this the proper way to do it? the RORData table is from a SQL View which has the WEEKEND date in it.

RORData:
SQL SELECT *
FROM $(DBHeader).vaRORData;

Join (RORData) Load QuarterName(WEEKEND) AS RORQuarter resident RORData;

Percentages not adding up correctly

Wed, 23 Feb 2011 21:28:25 GMT

I removed the QuarterName from the load script so I could still keep working on this problem, but nothing I've tried will get the TotalScoreGroup to add up to 100% for each quarter... seems like such a simple thing in Excel; not sure why I'm having so many issues on this chart.

Percentages not adding up correctly

johnw — Wed, 23 Feb 2011 21:52:48 GMT

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.

Percentages not adding up correctly

Wed, 23 Feb 2011 22:59:18 GMT

John I can't thank you enough for all your help! I used the dual function and it still didn't work. I then noticed I was using the Label name for the Quarter instead of the field name... as soon as I changed it to be the field they add up to 100% now. jeeze, what a nightmare...!