I am having trouble graphing an accurate average reference line on a chart. I tried figuring out the proper syntax to use for the line's calculated formula. Although the syntax I am using seems to be the most logical, or so it seems from what I have gathered from other Qlik Community threads, the value that it calculates does not match what I have calculated in Excel for the same measurement. What my colleague and I are focusing on is data across different states, regions, and sectors from multiple years. We want to graph two static reference lines, one that displays the average for a certain measurement Nationally and one that measures the average for a specific Region, so that data from states can be compared to those two values. Another addendum: we would like the average to be dynamic only when switching from year to year.
In an effort to graph a Regional average for Region 2, I entered this formula for the line:
avg({$<Region={'Region 2'}>}(M5AN+M5BN+M5CN+M5DN)/(M5AD+M5BD+M5CD+M5DD))
But, that yields a very different value than what I have for the same dimensions in Excel. If anyone has any tips for modifying that expression, please let me know, or if anyone has any other suggestions or questions, we would appreciate the help and support!
Emma L.
