Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
My main goal is to compare between two weeks over 2 different quarters, for example:
I want to see the total revenue until week number 5 in Q3 & Q4 2017
Thank you
Hi kfir,
try this
t:
Load Ceil(num(Date#(date,'DD/MM/YYYY')-QuarterStart(Date#(date,'DD/MM/YYYY')))/7)&' '&
QuarterName(Date#(date,'DD/MM/YYYY'))
as QuarterWeek;
Load * Inline
[date
01/02/2018
12/06/2018];
How do you define your week for quarter, is it calendar week that can make a quarter start from not necessarily 1st day of week? Or, is it always - the first day of quarter is starting of a quarter week, i.e first 7 days of quarter makes week 1?
the second choice is correct, the first day in the quarter will present the first week of the quarter
Then you can simply use QuarterStart() to get the starting date of the quarter and then add 7*weeknumber days to get the date. Something like:
to get - total revenue until week number 5 in Q3 2017
Sum({<Date={">=$(=MakeDate(2017,3*3)) <=$(=MakeDate(2017,3*3, 7*5))"}>}Revenue)
I hope the logic is clear. You might have to use QuarterStart()/QuarterEnd() instead of makedate() I used.
Thank you, but first I would like to add a new column with the week number. Then I would like to calculate the accumulative revenue column.
Thanks a lot.
Hi kfir,
try this
t:
Load Ceil(num(Date#(date,'DD/MM/YYYY')-QuarterStart(Date#(date,'DD/MM/YYYY')))/7)&' '&
QuarterName(Date#(date,'DD/MM/YYYY'))
as QuarterWeek;
Load * Inline
[date
01/02/2018
12/06/2018];
I tried it (I replaced the date with me date field) and it not working. I see only blanks cells
can you show a little sample that what u did, so that i can see why its showing null,
maybe there would be some problem in the date format
For creating week number field in the script, you could try something like:
Load
Ceil((Date-QuarterStart(Date))/7) as QuarterWeek