topic Re: Total Average not working in QlikView

Total Average not working

peterderrington — Wed, 25 Nov 2020 16:16:04 GMT

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:

Re: Total Average not working

Kushal_Chawda — Fri, 23 Oct 2020 14:05:43 GMT

@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))

Re: Total Average not working

peterderrington — Fri, 23 Oct 2020 14:15:18 GMT

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

Re: Total Average not working

Kushal_Chawda — Fri, 23 Oct 2020 14:51:06 GMT

@peterderrington missed aggr..typo

avg(total aggr(sum({<Sheet_name={'Wansbeck'},day={'Monday'},[Named Change]={'PreCovid'}>}WGH1),WeekYear))

Re: Total Average not working

peterderrington — Fri, 23 Oct 2020 14:54:22 GMT

Nope, that doesnt work - it looks like this now:

Re: Total Average not working

Kushal_Chawda — Fri, 23 Oct 2020 15:01:12 GMT

@peterderrington is the straight line generated showing correct values?

Re: Total Average not working

Chanty4u — Fri, 23 Oct 2020 15:09:07 GMT

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))

Re: Total Average not working

peterderrington — Fri, 23 Oct 2020 15:39:14 GMT

No that shows as an error in expression with the first ( highlighted in Red

Re: Total Average not working

peterderrington — Fri, 23 Oct 2020 15:40:29 GMT

Yes it showing an overall average of 2.35 which is correct for the entire time line

Re: Total Average not working

Chanty4u — Fri, 23 Oct 2020 15:49:23 GMT

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)))

Re: Total Average not working

peterderrington — Fri, 23 Oct 2020 15:54:48 GMT

Already tried - still an error

Re: Total Average not working

Kushal_Chawda — Fri, 23 Oct 2020 16:26:58 GMT

@peterderrington Then what is wrong?

Re: Total Average not working

peterderrington — Fri, 23 Oct 2020 16:49:45 GMT

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:

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

Re: Total Average not working

Brett_Bleess — Wed, 04 Nov 2020 20:30:43 GMT

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