topic Rolling weeks data in QlikView

Rolling weeks data

qlikapple — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

I have a datamodel in which i have shown the rolling 12 months data, now requiremnt is to show the rolling weeks data, can anyone please help,please find the datamodel here. i have the opendate in epoch format

table::

LOAD
NO,
OPENDATE,
floor(num(Timestamp('1970-01-01 00:00:00.000') + (OPENDATE)/60/60/24)) as CAL_OPENDATE,
STATUS,
FROM
table.qvd(qvd);

Temp:
Load
Min(CAL_OPEN_DATE) as minDate,
MonthEnd( Max(CAL_OPEN_DATE)) as maxDate
Resident table;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', -1, '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
floor(num(TempDate)) AS CAL_OPEN_DATE,
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
//WeekDay(TempDate) as WeekDay,
MonthName(TempDate) as MonthName,
Num(Month(TempDate)) as MonthNum
//if(month(weekend(TempDate))= month(TempDate),div(day(WeekEnd(TempDate)),7),div(day(Weekend(TempDate,-1)),7)+1)+1 as MonthWeekNumber
Resident TempCalendar
Order By TempDate ASC;

Drop Table TempCalendar;

after that i have taken the bar chart for rolling 12 months as

i have taken dimension as MonthName

and in the expressiona as below

count({<Year=,Month=,
MonthName={'>=$(=Date(addmonths(Max(MonthName), -12))) <=$(=Date((Max(MonthName))))'}>} distinct NO)

Now i want for rolling 4 weeks data, how to do, can anyone please help its very urgent.

Re: Rolling weeks data

qlikapple — Tue, 16 Apr 2019 16:40:37 GMT

anyoneplease help its very urgent

Re: Rolling weeks data

albertovarela — Tue, 16 Apr 2019 17:32:28 GMT

Try adding this to you MasterCalendar:

AutoNumber(weekyear(TempDate) &'|'&week(TempDate),'_WeekSerial') as WeekSerial

Then your expression can be defined as:

count({<Year=,Month=,WeekSerial={'>=$(=(Max(WeekSerial))-2) <=$(=(Max(WeekSerial)))'}>}>} distinct NO)

I hope it helps

Re: Rolling weeks data

qlikapple — Wed, 17 Apr 2019 03:53:17 GMT

Hi all,

i got it, but it is showing from current week data also, i dont want current week data, i want from previuos week to last 4 previous weeks data

the expression is

count({<Year=,Month=,WeekSerial={'>=$(=(Max(WeekSerial))-6) <=$(=(Max(WeekSerial))+1))'}>} distinct NO)

can anyon eplease help

Re: Rolling weeks data

albertovarela — Wed, 17 Apr 2019 04:03:26 GMT

Go with:

count({<Year=,Month=,WeekSerial={'>=$(=(Max(WeekSerial))-5) <=$(=(Max(WeekSerial))-1))'}>} distinct NO)

Re: Rolling weeks data

qlikapple — Wed, 17 Apr 2019 04:12:31 GMT

HI can anyone please help, its very urgent

Re: Rolling weeks data

qlikapple — Wed, 17 Apr 2019 04:18:23 GMT

Hi all,

i need the expression for last 4 weeks rolling data

and also i need to show the diffrence of count in last 2 weeks in separate column as up and down arrow,

can anyon eplease hep ,its very urgent

Re: Rolling weeks data

albertovarela — Wed, 17 Apr 2019 15:22:23 GMT

My bad I was throwing a few extra parenthesis on previous reply

Count({<Year=,Month=,WeekSerial={'>=$(=Max(WeekSerial)-4) <=$(=Max(WeekSerial)-1)'}>} Distinct NO)

Re: Rolling weeks data

qlikapple — Wed, 17 Apr 2019 04:54:58 GMT

HI, i am getting fro week 13,14,15 and 16 data i dont want 16 data, i want 12,13,14 and 15 data. may i know how to work on Count({=$(=Max(WeekSerial)-5) <=$(=Max(WeekSerial)-1)'}>} Distinct NO)

Re: Rolling weeks data

qlikapple — Wed, 17 Apr 2019 05:32:08 GMT

I am getting for week 12,13,14 ,15 and 16 week data.. I want to show only 12,13,14 and 15 week data and in seperate column i need to show the diffrence of count between 15 week and 14th week . means for 15 week the no is 56 and for the 14th week the no is 50, i want to show in sepate column as 6

Re: Rolling weeks data

albertovarela — Wed, 17 Apr 2019 15:32:31 GMT

tweak accordingly:

This can be reused for that additional column