Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I'm trying to show a changing average line on my chart but i can't seem to work out how to write the expressions.
The red line shows the overall average over the whole time (it uses this expression - avg(TOTAL AGGR(Sum({<Sheet_name={'Wansbeck'},day={'Monday'}>}WGH1),Date)) Despite the dimension being WeekYear 😞
I have 3 conditions (PreCovid, Covid & HomeSafe Weekend) that i want to display the averages for.
They run like this:
Week23 2019 - Week 11 2020 = PreCovid
Week 12 2020 - Week 18 2020 = Covid
Week 19 2020 - Week 31 2020 = HomeSafe Weekend
When i try the expression = avg(Total Aggr(Avg({<Sheet_name={'Wansbeck'},day={'Monday'},[Named Change]={'PreCovid'}>}WGH1),MonthYear)) I just get this:
Which just overwrites the overall average.
If i try: avg(Aggr(Avg({<Sheet_name={'Wansbeck'},day={'Monday'},[Named Change]={'PreCovid'}>}WGH1),MonthYear)) Then as you can the average line ends but it ends early (Week 9 instead of Week 11) but also its not a true average as that should be solid at 2.51
What am i doing wrong and ideally how could i show one line changing between the three states?
Here is a shot of the data in a straight table:
@peterderrington Is it because you are using MonthYear instead of WeekYear in aggr?
avg(total sum({<Sheet_name={'Wansbeck'},day={'Monday'},[Named Change]={'PreCovid'}>}WGH1),WeekYear))
No, i tried your expression and it just said that the expression was wrong apparently after WGH1) its garbage
I did however adapt the original one to WeekYear not Month Year - avg(Aggr(Avg({<Sheet_name={'Wansbeck'},day={'Monday'},[Named Change]={'PreCovid'}>}WGH1),WeekYear))
and now it continues to the end of the section which is great but it doesn't show a straight line (if i add Total then it just goes to the very end which is still wrong)
We're getting close....
@peterderrington missed aggr..typo
avg(total aggr(sum({<Sheet_name={'Wansbeck'},day={'Monday'},[Named Change]={'PreCovid'}>}WGH1),WeekYear))
Nope, that doesnt work - it looks like this now:
@peterderrington is the straight line generated showing correct values?
can you try this?
avg(total aggr(sum({<Sheet_name={'Wansbeck'},day={'Monday'},[Named Change]={'PreCovid'}>}WGH1/sum({<Sheet_name={'Wansbeck'},day={'Monday'},[Named Change]={'PreCovid'}>}WGH1),WeekYear))
No that shows as an error in expression with the first ( highlighted in Red
Yes it showing an overall average of 2.35 which is correct for the entire time line
only close is missing .. can you try this
avg(total aggr(sum({<Sheet_name={'Wansbeck'},day={'Monday'},[Named Change]={'PreCovid'}>}WGH1/sum({<Sheet_name={'Wansbeck'},day={'Monday'},[Named Change]={'PreCovid'}>}WGH1),WeekYear)))