How to calculate week number in the quarter

[kfir1987]

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

[tresesco]

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?

[kfir1987]

the second choice is correct, the first day in the quarter will present the first week of the quarter

[tresesco]

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.

[kfir1987]

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.

[vishus913 · Accepted solution]

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];

[kfir1987]

I tried it (I replaced the date with me date field) and it not working. I see only blanks cells

[vishus913]

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

[tresesco]

For creating week number field in the script, you could try something like:

Load

          Ceil((Date-QuarterStart(Date))/7) as QuarterWeek