Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
peterderrington
Creator II
Creator II

Total Average not working

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 😞

1 .jpeg

 

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:

2 .jpeg

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

3 .jpeg

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:

4.jpg

 

 

13 Replies
peterderrington
Creator II
Creator II
Author

Already tried - still an error

7.jpg

Kushal_Chawda

@peterderrington Then what is wrong?

peterderrington
Creator II
Creator II
Author

Well as i said earlier @Kushal_Chawda , i want to show an overall average (the red line) and also an average line that shows the average for a set period, PreCovid, Covid and HomeSafe on the same graph. So those other three lines should be flat not wavy, 

Similar to this:

8.jpeg

As you can see, the green line has three separate values to it dependant on which time segment they're referring to. 

Brett_Bleess
Former Employee
Former Employee

Best I have to try to help is the following Design Blog post:

https://community.qlik.com/t5/Qlik-Design-Blog/Average-Which-average/ba-p/1466654

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.