Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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.
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)
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.
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
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.
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.
Hahahaha ya that I definitely agree .
Jagan and Sunny both are correct. Smooth and straight
Hi,
please see this
Calculating rolling n-period totals, averages or other aggregations
or
this issue is solved?
HTH
André Gomes