Hello community,
I'm having trouble getting my set expression to display percent of total correctly. In my line chart, I have it set to display a rolling four year period (e.g., each of the last 4 years based upon the year selected by the user), using the following two defined variables and the set expression:
vMinYear = if(GetSelectedCount(Year)=1,AddYears(Max(date),-4))
vYearSelected = if(GetSelectedCount(Year)=1,Max(date))
=Sum({$<Year=,date= {'>$(vMinYear) <=$(vYearSelected)'}>}Responses)
The above set gives me the correct sums (i.e., sum of responses) for the year(s) in question. What I need, though, is the percent of total for those years. Some my question is: what would I use as the denominator in this set to get the correct % of Total:
=Sum({$<Year=,date= {'>$(vMinYear) <=$(vYearSelected)'}>}Responses) / ?????
Below are some attempts that didn't work:
=Sum({$<Year=,date= {'>$(vMinYear) <=$(vYearSelected)'}>}Responses) / Sum(TOTAL Responses)
--- it gives the correct % of Total only for the last year selected, but not for any of the earlier years. (e.g., if user selects 2017, correct % of total is displayed for 2017, but not for 2016, 2015, or 2014). Through some digging I discovered that Sum(TOTAL Responses) is returning total only for the currently selected year, so it won't work as the denominator of the earlier years (whose base totals are different)
=Sum({$<Year=,date= {'>$(vMinYear) <=$(vYearSelected)'}>}Responses) / Sum(TOTAL {$<Year=,date= {'>$(vMinYear) <=$(vYearSelected)'}>}Responses)
---gives the incorrect % of total for all years. In this case, the set in the denominator is returning the total for all 4 years.
For illustration purposes, if the totals for Year4 (selected year) is 20, Year3 is 30, Year2 is 30, and Year1 is 50...
... Sum(TOTAL Responses) is using 20 as the denominator, which is incorrect
...Sum(TOTAL {$<Year=,date= {'>$(vMinYear) <=$(vYearSelected)'}>}Responses) is using 130 as the denominator, which isn't correct
I'd need the set to divide the sum of each year by its respective total (e.g., sum Year4 / 20, and sum Year3 / 30, etc).
Any suggestions?
Thanks in advance,
John
