Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Get week number within a quarter

Suppu
6 - Meteoroid

Hi,

 

How can I get a week number within a quarter.

For example

DateWeek in Quarter
4/3/20211
4/30/20215
5/7/20216
12/20/202113
6/20/202113
6/30/202114
9/14/202112
9 REPLIES 9
jdunkerley79
ACE Emeritus
ACE Emeritus

Slightly twisted but:

 

ToNumber(DateTimeFormat([Date],"%U"))
- ToNumber(DateTimeFormat(
   Left([Date],5) +
   PadLeft(ToString(Floor((DateTimeMonth([Date])-1)/3)*3+1),2,'0')
   + '-01', "%U"))
+ 1

 

should do it.

 

DateTimeFormat will get the week number. The second one works out the week number at the start of the quarter. Finally adding a 1 to ensure first week is 1

 

mceleavey
17 - Castor
17 - Castor

Hi @Suppu ,

 

I'm not sure I totally understand, but I think you need the week number from the beginning of the fiscal year, correct?

So, for example, your first date provided is 3rd April 2021 (I assume it's in the US format), and you've provided the week as 1, so I assume the first quarter starts 1st April.

I've built it on this assumption:

 

mceleavey_1-1630067629838.png

 

I converted the date then worked out the week number off-set by 12 (the first 12 weeks of the calendar year:

 

(if tonumber(datetimeformat([DateTime_Out],"%W"))<=12 then tonumber(datetimeformat([DateTime_Out],"%W")) +53 else tonumber(datetimeformat([DateTime_Out],"%W")) endif)-12

 

Hope this helps,

 

M

 

 

 

 

 



Bulien

Suppu
6 - Meteoroid

Hello,

Thank you for your answer . Could you please break down below part so that I can understand :

PadLeft(ToString(Floor((DateTimeMonth([Date])-1)/3)*3+1),2,'0')+'-01', "%U"))

Suppu
6 - Meteoroid

Hello,@mceleavey

 

I need the week number in a quarter . Jan-Mar 13/14 week. If I put in a date then I need to get week number in the quarter so essentially it will differ from a normal week number (which has 53 max) form April onwards. So 1st April will be week 1 then 1st July will be 1 then 1st October and so on. I have provided a sample table .

Kindly reply in case I can provide more information.

 

Supreet

jdunkerley79
ACE Emeritus
ACE Emeritus

The expression 'PadLeft(ToString(Floor((DateTimeMonth([Date])-1)/3)*3+1),2,'0')'

 

- takes the month of the date (DateTimeMonth([Date]))

- it then maps it to a quarter (0-3) by shifting back by 1 and dividing by 3 and then taking the floor (Floor((month-1)/3))

- next it turns this into the month number (1, 4, 7, 10) for the start of the quarter (quarter*3+1)

- finally it converts to a string and puts a leading 0 in front of it (PadLeft(month,2,'0'))

 

Hope that makes sense

jdunkerley79
ACE Emeritus
ACE Emeritus

Failed to attach my example workflow.

 

So have attached here

atcodedog05
22 - Nova
22 - Nova

Hi @Suppu 

 

Amazing approach by @jdunkerley79 I guess I might have ended with a similar approach. Here is the breakdown of the formula. I have broken down into mutiple formulas for your understanding.

 

Edit: I can see that @jdunkerley79  has already provided an explanation you can use mine for addtional reference 😅

 

atcodedog05_0-1630071167948.png

 

%U gives yearly week number.

 

If you are still facing any issues let me know.

 

Hope this helps : )

Suppu
6 - Meteoroid

Hi @jdunkerley79,@atcodedog05, 

 

Many Many thanks for the solution and the detailed explanation.

It helped me a lot.

 

Supreet

atcodedog05
22 - Nova
22 - Nova

Happy to help : ) @Suppu 

Cheers and have a nice day!

Labels