Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)'}>}
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
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
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.