Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Senor_Dai
Partner - Creator II
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)
1 Solution

Accepted Solutions
Senor_Dai
Partner - Creator II
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!

View solution in original post

7 Replies
Anil_Babu_Samineni

You mean something this?

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

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)
MeehyeOh
Partner - Creator
Partner - Creator

Hi, @Senor_Dai 

How do I set the start of the week?

 

Reply, thanks!

G3S
Creator III
Creator III

WeekStart(Date)

MeehyeOh
Partner - Creator
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?

Senor_Dai
Partner - Creator II
Partner - Creator II
Author

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
Partner - Creator II
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!

MeehyeOh
Partner - Creator
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:
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