Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
shree909
Partner - Specialist II
Partner - Specialist II

Week to week and quarter to quarter comparison

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

4 Replies
Gysbert_Wassenaar

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)'}>}


talk is cheap, supply exceeds demand
shree909
Partner - Specialist II
Partner - Specialist II
Author

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,,,

week.PNG.png

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

preminqlik
Specialist II
Specialist II

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

Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand