Calculate 4 Week Average over Last 6 Months

[Senor_Dai]

Hi,

I have the following fields  

• Date
• Flag_Session_in_Last6Months : '1 'or '0'
• Flag_Session_in_Last4Weeks : '1 'or '0'
• Session_Count

Id like to be able to compare the total sessions for the last 4 weeks to a '4-Week Average' of the last 6 months...  Id really appreciate any advise on the correct expression on working out 4 week average of the last 6 months?

Many thanks,

Dai

[Anil_Babu_Samineni]

You mean something this?

Sum(Total <Flag_Session_in_Last6Months> {<Flag_Session_in_Last4Weeks={1}>} Session_Count)

[MeehyeOh]

Hi, @Senor_Dai 

How do I set the start of the week?

Reply, thanks!

[G3S]

WeekStart(Date)

[MeehyeOh]

No, I want to make sure that what Senor_Dai  wants is to calculate four weeks of each day, or four weeks based on a certain day of the week, like Monday or Sunday.

@Senor_Dai, Can you please provide a sample data and the required outcome?

[Senor_Dai]

HI @MeehyeOh 

Our Usage App reloads every night so we have a rolling 4 week and rolling prior 6 month flag.  So as at today  the previous 6 months covers 12th May 2023 to 10th October and the previous  4 weeks flag covers 11 Oct to 7th November.

Say ,for example, over the last 28 days (45 weeks) our users have 3.8K Sessions and over the previous 6 months there have been 22K sessions.  

I'd like to show if the 4 week average over the last 6 months is higher or lower than the previous 4 week actual.

Hope this helps?

Dai

[Senor_Dai · Accepted solution]

Hi All, 

think I have sorted it :

round((sum({<Flag_Session_Last_6Months={1}>}[Session Count])/26)*4)

So,  Ive used 182 days to set the 6 month flag.  I divide the total sessions  by 26 (this equates to 26 weeks) and then multiply this by 4 to get the 4 week average.

thanks for everyones input/replies . Much appreciated!

[MeehyeOh]

Hi, @Senor_Dai 

I don't understand..

If you divide the total sum during 26 weeks by 26 and multiply by 4, it's just the average of the last 26 weeks multiplied by 4.

That's not the average on a four-week basis for the last 26 weeks.

First of all, I think you should create a field that calculates the previous weeks in the script.

Like the script below.

--

LET vMinDate = Num(Date#('20230401','YYYYMMDD'));

LET vMaxDate = Num(Today());

TempCalendar:

LOAD

$(vMinDate) + Iterno()-1 As Num,

    Date($(vMinDate) + IterNo() - 1,'YYYYMMDD') as TempDate

AutoGenerate 1 

While $(vMinDate) + IterNo() -1 <= $(vMaxDate);

TMP:

Load

*,

    Ceil(LastWeek/4) As LastWeek_4

;

Load

TempDate As DATE,

    Month(TempDate) As Month,

    Week(TempDate) As Week,

    If(TempDate >= Today()-182 And TempDate < Today(),1,0) As Flag_Session_in_Last6Months,

    If(TempDate >= Today()-28 And TempDate < Today(),1,0) As Flag_Session_in_Last4Weeks,

    Floor((TempDate - $(vMaxDate))/7)*-1 As LastWeek,

    Floor(rand()*50) As Session_Count

Resident TempCalendar;

Drop Table TempCalendar;

--

In my script, TempDate is Date and $(vMaxDate) is Today().
For your script, $(vMaxDate) can be the date of reload every night.

Then use aggr function in chart expression.

Like the expression below.

Avg(Aggr(Avg({< Flag_Session_in_Last6Months = {1}, LastWeek -= {0} >}Session_Count),LastWeek_4))

Isn't this how can get the average of the four-week average over the last 26 weeks...?

Please rply, thanks