Average past 28 days

[jlampard40]

Hi guys - I have this set analysis which is working for calculating the average demand per day:

=Num(avg({<Financial_Year={'FY19/20'}, Activity_or_Demand={'Demand'}>}
Aggr(count({<Financial_Year={'FY19/20'},Activity_or_Demand={'Demand'}>}
Event_Key),Request_Date, Individual_Exam_Parts_Per_Event)),'#,###.')

How can I modify it to show the average demand over the past 28 weeks?  I have fields 'Request_Date' in format DD/MM/YYYY which are daily dates so somehow use the today() function -28 somehow??  Many thanks.

[martinpohl]

so change both

Financial_Year={'FY19/20'}

to

Request_Date={"<=date(today(),'DD/MM/YYYY)>=date(today()-27,'DD/MM/YYYY)"}

Regards

p.s.

you want days or weeks? 2 time you wrote 28 days, one time 28 weeks

[jlampard40]

Sorry - 28 days.  Many thanks for reply.  

[Anil_Babu_Samineni]

I would always suggest to create field called week in script

Ex: Week(Request_Date) as Week

Expression

=Num(avg({<Week={">=$(=Max(Week-26))<=$(=Max(Week))"}, Financial_Year={'FY19/20'}, Activity_or_Demand={'Demand'}>}
Aggr(count({<Week={">=$(=Max(Week-26))<=$(=Max(Week))"}, Financial_Year={'FY19/20'},Activity_or_Demand={'Demand'}>}
Event_Key),Request_Date, Individual_Exam_Parts_Per_Event)),'#,###.')

[jlampard40]

For some reason that's not working Martinpohl...any ideas why?

[jlampard40]

That works a treat Anil!  However, on the top line of my straight table, the expression total is not at all what I would expect.  The individual lines are fine, per Individual_Exam_Parts_Per_Event but the total at the top is completely wrong.  What am I missing?  Many thanks for your persistence.

[Anil_Babu_Samineni]

I would always suggest first place with Outer aggregation as well some cases? Perhaps this

Num(Sum(Aggr(avg({<Week={">=$(=Max(Week-26))<=$(=Max(Week))"}, Financial_Year={'FY19/20'}, Activity_or_Demand={'Demand'}>}
Aggr(count({<Week={">=$(=Max(Week-26))<=$(=Max(Week))"}, Financial_Year={'FY19/20'},Activity_or_Demand={'Demand'}>}
Event_Key),Request_Date, Individual_Exam_Parts_Per_Event)), Individual_Exam_Parts_Per_Event)), '#,###.')

[martinpohl]

sorry, I missed two figures

Request_Date={"<=date(today(),'DD/MM/YYYY')>=date(today()-27,'DD/MM/YYYY')"}

Regards

[Brett_Bleess]

If any of the posts helped you with your use case, do not forget to return to your post and use the Accept as Solution button on the post(s) that helped you get things working as desired.  This gives credit to the poster and lets other Community Members know what actually worked.

Not sure if you are aware of the Design Blog area either, so I am going to post the base URL here for you, there are hundreds of how-to posts from our experts in this area, so be sure to specifically search there in the future, as you will likely be able to find quite a few useful things there.  There are some posts on Set Analysis related to dates and other concepts.

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Specific one on dates:

https://community.qlik.com/t5/Qlik-Design-Blog/Dates-in-Set-Analysis/ba-p/1472511

Regards,
Brett