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?
May be create WeekYear like this in the script:
Dual(Week(Weekstart(TempDate,0,7)) & '-' & WeekYear(Today()), Num(Week(Weekstart(TempDate,0,7)) & WeekYear(Today()))) as WeekYear
and now even though it will look like 35-2015, it will be a number 352015 and to go back 12 Week you can subtract 110000 to get 242015
HTH
Best,
Sunny
in addition to the above a suggestion. Whenever we are trying to work on sub part of dates, i.e. months, weeks or days always associate these values with the year or month, because these values are not unique for a given year. Hence always try to associate them with their parent value to make values unique.
hi Sunny
what if the week is 10?
Hi Michael,
Try like this
Dimension: WeekYear
Expression: =Sum({<Year=, Month=, Week=, MonthYear=, WeekYear=, TransDate={'>=$(=WeekStart(Max(TransDate), -11))<=$(=Date(Max(TransDate)))'}>} Sales)
Regards,
Jagan.
Hahahaha you guys are right . The logic will have to be more complex in that case. Will need a better solution then what I proposed.
Best,
Sunny
May be try like this
Expression: =Sum({<Year=, Month=, Week=, MonthYear=, WeekYear=, TransDate={'>=$(=weekstart(MakeWeekDate( subfield(MaxString(WeekYear),'-',2) ,subfield(MaxString(WeekYear),'-',1) ),-11)))<=$(=weekstart(MakeWeekDate( subfield(MaxString(WeekYear),'-',2) ,subfield(MaxString(WeekYear),'-',1) ))'}>} Sales)
please check the expressions in a text box to see if it returning the correct date range
=weekstart(MakeWeekDate( subfield(MaxString(WeekYear),'-',2) ,subfield(MaxString(WeekYear),'-',1) ),-11)
=weekstart(MakeWeekDate( subfield(MaxString(WeekYear),'-',2) ,subfield(MaxString(WeekYear),'-',1) )
sasi
Hi Michael, did you able to achieve the answer.
This seems like a good solution. Here is the script:
Table:
LOAD *,
Ceil(Rand() * 100000) as Value;
LOAD Date(YearStart(42245-365) + RecNo()) as Date
AutoGenerate 730;
QuartersMap:
MAPPING
LOAD RowNo() as Month,
'Q' & Ceil (RowNo()/3) as Quarter
AUTOGENERATE (12);
Temp:
Load Min(Date) as minDate,
Max(Date) as maxDate
Resident Table;
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
TempCalendar:
LOAD $(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
MasterCalendar:
Load TempDate AS Date,
Week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
YeartoDate(TempDate)*-1 as CurYTDFlag,
YeartoDate(TempDate,-1)*-1 as LastYTDFlag,
InYear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,
Date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
Dual(Week(WeekStart(TempDate)) & '-' & WeekYear(TempDate), AutoNumber(Week(WeekStart(TempDate)) & '-' & WeekYear(TempDate))) as WeekYear,
WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
So when i add WeekYear to a chart dimension with Num(WeekYear) I get this:
To get 12 Weeks of Data I can use this in my set analysis:
=Sum({<WeekYear = {"=Num(WeekYear) >= Max(TOTAL WeekYear) - 11"}>}Value)
=Sum({<WeekYear = {"=Num(Only({<WeekYear = >}WeekYear)) >= Max(TOTAL WeekYear) - 11 and
Num(Only({<WeekYear = >}WeekYear)) <= Max(TOTAL WeekYear)"},
Date = , Week, Year = , Month = , Day = , MonthYear = , Quarter =>} Value)
Output in a bar chart with no selection:
Output in a bar chart with Selection in WeekYear (50-2015)
Thanks for all of this. I think it's almost there, but there's one issue: A selection on MonthYear or WeekEnd doesn't have the expected result. Those are the two fields the users are actually seeing in the app. For example, if I select Jul-2015 in MonthYear in the QVW you shared, I get five bars, instead of a rolling 12 ending in July of 2015.
I'm looking at your set analysis, but I'm not sure yet how it should be changed. Any ideas?
Basically, I want the user to be able to select a date or range of dates in the MonthYear and WeekEnd fields, and have a rolling 12 weeks ending with whatever the maximum week is they have selected.