In case you missed the announcement: Alteryx One is here, and so is the Spring Release! Learn more about these new and exciting releases here!

Alteryx Designer Desktop Discussions

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

Calculating total days in that quarter by using a date.

Alteryxexpert
8 - Asteroid

I have data like below.

 

Date

2024-01-01

2024-02-01

2024-03-01

 

I need to calculate quarter days across each date.

 

2024-01-01 - 91(sum of jan,feb,mar 24 days)

2024-02-01 - 91

2024-03-01 - 91

 

And for 

2024-04-01 - 91 ( sum of apr,may,jun 24 days)

 

How can this be achieved?

 

8 REPLIES 8
Yoshiro_Fujimori
15 - Aurora
15 - Aurora

Hi @Alteryxexpert ,

 

Do you mean like this?

I hope this helps.

 

Workflow

1282326_Workflow.png

Output

Date3 months laterdiff
2024-01-012024-04-0191
2024-02-012024-05-0190
2024-03-012024-06-0192

 

Formula

3 months later = DateTimeAdd([Date],3,"month")

diff = DateTimeDiff([3 months later],[Date],"day")

Alteryxexpert
8 - Asteroid

Thanks for the response.

The actual ask is if the date is 2024-01-01(Jan 2024) then it is Q1 2024 then the total days should be returned as sum of days in (jan , feb , mar 24) which is 31+29+31 = 91 for all the three months in that quarter it should return 91.
if the date is 2024-04-01(Apr 2024) then it is Q2 2024 then the total days should be returned as sum of days in (apr,May,Jun 24) which is 30+31+30 = 91 for all the three months in that quarter it should return 91.

In your case for Jan , Feb , mar the days count is changing which is not the case, so taking difference between two dates won't work, any other way to do this?

aatalai
14 - Magnetar

@Alteryxexpert take a look at the attached workflow, it should achieve what you are looking for

Krzysiek
8 - Asteroid

Hi

 

Below workflows checks for 'in which quarter is given month'

Then is showing number of days for a given quarter.

 

That should be something you are looking for :)


Capture.PNG

 

Alteryxexpert
8 - Asteroid

@Krzysiek  This won't work when it is a leap year or non leap year. And I don't want to hardcode 91,92 across Q1 Q1 , I want Alteryx to compute the numbers.

Alteryxexpert
8 - Asteroid

I'm facing the attached error in the CEIL Function.

Alteryxexpert
8 - Asteroid

@aatalai I'm getting the attached error while using your logic

Calvin6KV
6 - Meteoroid

@Alteryxexpert I gave this problem a shot and here attached is my workflow. I first calculated the start and end dates for each quarter based on the date provided, and then compute the difference between the 2 dates. Do let me know if it meets your requirements.

Labels
Top Solution Authors