topic Re: Week to week and quarter to quarter comparison in QlikView

Week to week and quarter to quarter comparison

shree909 — Fri, 03 Jan 2014 16:35:11 GMT

Hi Folks,

I need some help help in comparing the current week and previous week for the max year and max month.

i used this expression for calculating the weeks

'Week-'&(1+CEIL((FLOOR([Accessed Date])-FLOOR(WEEKEND(MONTHSTART([Accessed Date]),0,-1)))/7)) AS Week,

so i i get Week-1,week-2,week-3,week-4,week-5..

now i want to write an expression for current week selected and previous week which has to be dyanmic , depending on the week selected

and comparison between quarters

'Q' & CEIL(MONTH([Accessed Date])/3) AS [Quarter],

Q2 and Q1 expression depending on the quarter selected... current Quarter and Previous Quarter for the Year selected.

Thanks

Re: Week to week and quarter to quarter comparison

Gysbert_Wassenaar — Fri, 03 Jan 2014 16:46:27 GMT

Create numeric serial week and quarter fields:

autonumber(weekstart([Accessed Date]),'WeekSerial') as _WeekSerial

autonumber(quarterstart([Accessed Date]),'QuarterSerial') as _QuarterSerial

You can then use these new fields in set analysis expressions:

current week: sum({<_WeekSerial={'$(=max(_WeekSerial))'}>}

previous week: sum({<Year=,Month=, _WeekSerial={'$(=max(_WeekSerial)-1)'}>}

Re: Week to week and quarter to quarter comparison

shree909 — Fri, 03 Jan 2014 18:03:55 GMT

Thanks for the reply when i am using the Autonumber for quarter i am getting 6 quarters which are not matching with the Q1,Q2,Q3,Q4 and same thing happens for Week,,,

Moreover i don't have continuous dates for the years 2012 and 2013..so how can i use ur calcutions that can match with Quarter and Week.

Thanks

Re: Week to week and quarter to quarter comparison

preminqlik — Fri, 03 Jan 2014 18:24:00 GMT

take inline

as below:

week:

load *

inline [

Day,Week

1,1

2,1

3,1

4,1

5,1

6,1

7,1

8,2

9,2

10,2

11,2

12,2

13,2

14,2

15,3

16,3

17,3

18,3

19,3

20,3

21,3

22,4

23,4

24,4

25,4

26,4

27,4

28,4

29,4

30,4

31,4

];

and apply mapp it or right join it to master calendar

and then do

current week --->sum({Year={'$(=max(Year))'},Month={'$(=Max(Monthnum))'},Week={'$(=max(Week)'},Date={'<=$(=Max(Date)'}})

Previous week ---> sum({Year={'$(=max(Year))'},Month={'$(=Max(Monthnum))'},Week={'$(=max(Week)-1'},Date={'<=$(=Max(Date)'}})

hope this helps you

Regards

Prem

Re: Week to week and quarter to quarter comparison

Gysbert_Wassenaar — Fri, 03 Jan 2014 18:35:21 GMT

Create a master calendar that will generate all the dates. See this discussion: Tutorial - Using Common Date Dimensions and Shared Calendars. If you use the QVC library to generate the master calendar you automagically some _*Serial fields too. Those are not meant to be used in listboxes, but only in expressions.

I'll try to explain the problem with the Q1, Q2 etc. Suppose you select 2013 and Q1. What's the previous quarter? It's not Q0. That doesn't exist. It's not possible to take a value from the Quarter field and subtract 1 from it to get the previous quarter. With a serial field this is possible. It will have for example values 1 for Q1-2012, 2 for Q2-2012, 3 for Q3-2012, 4 for Q4-2012 and 5 for Q1-2013. Now if you select 2013 in the year field and Q1 in the quarter field you should see only value 5 as possible value in the _QuarterSerial field. Since it's a numeric field you can subtract 1 from it: 5 - 1 = 4. And value 4 represents Q4-2012, which is indeed the previous quarter of Q1-2013.