topic Re: Rolling 12 Weeks in QlikView

Rolling 12 Weeks

mhassinger — Thu, 27 Aug 2015 20:27:09 GMT

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?

Re: Rolling 12 Weeks

sunny_talwar — Thu, 27 Aug 2015 20:33:04 GMT

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

Re: Rolling 12 Weeks

harsh44_bhatia — Thu, 27 Aug 2015 20:58:26 GMT

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.

Re: Rolling 12 Weeks

sasiparupudi1 — Fri, 28 Aug 2015 06:27:44 GMT

hi Sunny

what if the week is 10?

Re: Rolling 12 Weeks

jagan — Fri, 28 Aug 2015 07:13:40 GMT

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.

Re: Rolling 12 Weeks

sunny_talwar — Fri, 28 Aug 2015 07:15:38 GMT

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

Re: Rolling 12 Weeks

sasiparupudi1 — Fri, 28 Aug 2015 08:17:41 GMT

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

Re: Rolling 12 Weeks

qlikviewwizard — Sat, 29 Aug 2015 08:24:09 GMT

Hi Michael, did you able to achieve the answer.

Re: Rolling 12 Weeks

sunny_talwar — Sat, 29 Aug 2015 10:06:51 GMT

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)

Re: Rolling 12 Weeks

mhassinger — Thu, 03 Sep 2015 03:04:38 GMT

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.

Re: Rolling 12 Weeks

jagan — Thu, 03 Sep 2015 03:16:21 GMT

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.

Re: Rolling 12 Weeks

sunny_talwar — Thu, 03 Sep 2015 03:23:32 GMT

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)

Re: Rolling 12 Weeks

jagan — Thu, 03 Sep 2015 03:38:20 GMT

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.

Re: Rolling 12 Weeks

sunny_talwar — Thu, 03 Sep 2015 07:05:34 GMT

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

Re: Rolling 12 Weeks

qlikviewwizard — Thu, 03 Sep 2015 07:26:53 GMT

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.

Re: Rolling 12 Weeks

jagan — Thu, 03 Sep 2015 07:37:45 GMT

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.

Re: Rolling 12 Weeks

sunny_talwar — Thu, 03 Sep 2015 07:41:11 GMT

Hahahaha ya that I definitely agree .

Re: Rolling 12 Weeks

qlikviewwizard — Thu, 03 Sep 2015 07:55:54 GMT

Jagan and Sunny both are correct. Smooth and straight

Re: Rolling 12 Weeks

agomes1971 — Mon, 29 Aug 2016 17:24:53 GMT

Hi,

please see this

Calculating rolling n-period totals, averages or other aggregations

this issue is solved?

HTH

André Gomes