Rolling 12 Weeks

[mhassinger]

I'm trying to figure out the best way to show a chart that doing rolling 12 weeks. I've got the months, no problem, with:

=SUM({<MonthYear={'>=$(=Max(MonthYear)-11)<=$(=Max(MonthYear))'}>}$(vNetSales))/$(vPrecision)

The issue is I can't just subtract 11 from the WeekYear field like I can from MonthYear. My Calendar definition is:

Load 

               TempDate AS TransDate,  

               date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,

               week(TempDate) As Week,

               Year(TempDate) As Year, 

               Month(TempDate) As Month,

               Day(TempDate) As Day, 

               Week(weekstart(TempDate,0,7)) & '-' & WeekYear(TempDate) as WeekYear,

               WeekEnd(TempDate,0,7) As WeekEnd

Any ideas?

[jagan]

Hi,

You can try like this using the Date field

=Sum({<WeekYear =, Week=, Year = , Month = , Day = , MonthYear = , Quarter =, Date = {'>=$(=WeekStart(Max(Date), -11))<=$(=WeekEnd(Max(Date)))'}>} Value)

For more details you can check below link

Set Analysis for certain Point in Time

Hope this helps you.

Regards,

Jagan.

[sunny_talwar]

Small change in the expression:

=Sum({<WeekYear = {"=Num(Only({<WeekYear = , Date = , Week = , Year = , Month = , Day = , MonthYear = , Quarter =>}WeekYear)) >= Max(TOTAL WeekYear) - 11 and

  Num(Only({<WeekYear = , Date = , Week = , Year = , Month = , Day = , MonthYear = , Quarter =>}WeekYear)) <= Max(TOTAL WeekYear)"},

Date = , Week = , Year = , Month = , Day = , MonthYear = , Quarter =>} Value)

[jagan]

Hi Sunny,

If you do this based on the Date field it will be more flexible and easier to manage the expressions, the same thing you can achieve by using below expression

Last 15 Weeks Sales

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=WeekStart(Max(Date), -14))<=$(=Date(Max(Date)))’}>} Sales )

Check this link more expressions like this

Set Analysis for Rolling Periods

Regards,

Jagan.

[sunny_talwar]

Hey Jagan -

I agree, dates are easier to use, but I think listing out other possibilities can prove useful for the user or others in the future

Best,

Sunny

[qlikviewwizard]

Hi Please check Jagan's threads:

Set Analysis for Rolling Periods‌

Set Analysis for certain Point in Time

Hope this will help you.

Thank you.

[jagan]

Hi Sunny,

The expressions should be simpler and efficient that is what I am trying to say.  May be your expression works fine, but difficult one.

Regards,

Jagan.

[sunny_talwar]

Hahahaha ya that I definitely agree .

[qlikviewwizard]

Jagan and Sunny both are correct. Smooth and straight

[agomes1971]

Hi,

please see this

Calculating rolling n-period totals, averages or other aggregations

or

this issue is solved?

HTH

André Gomes