Announcements
cancel
Showing results for
Did you mean:
Partner - Creator II

Calculate 4 Week Average over Last 6 Months

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

Labels (2)

• Set Analysis

1 Solution

Accepted Solutions
Partner - Creator II
Author

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!

7 Replies

You mean something this?

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Partner - Creator

Hi, @Senor_Dai

How do I set the start of the week?

Creator III

WeekStart(Date)

Partner - Creator

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?

Partner - Creator II
Author

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

Partner - Creator II
Author

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!

Partner - Creator

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:
\$(vMinDate) + Iterno()-1 As Num,
Date(\$(vMinDate) + IterNo() - 1,'YYYYMMDD') as TempDate
AutoGenerate 1
While \$(vMinDate) + IterNo() -1 <= \$(vMaxDate);

TMP:
*,
Ceil(LastWeek/4) As LastWeek_4
;
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...?