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?

[sunny_talwar]

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

[harsh44_bhatia]

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.

[sasiparupudi1]

hi Sunny

what if the week is 10?

[jagan]

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.

[sunny_talwar]

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

[sasiparupudi1]

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

[qlikviewwizard]

Hi Michael, did you able to achieve the answer.

[sunny_talwar]

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)

[mhassinger]

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.