Percentages not adding up correctly

[#1]

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!

[#2]

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)

[#3]

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;

[#4]

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.

[johnw · Accepted solution]

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.

[#6]

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...!