Help regarding Alteryx constants and variables

Hello,

I have to perform some quarterly calculations for the previous 5 years using each quarter ending date. So, now it begins from 2016Q1 and ends at 20202Q1. I have stored these dates as constants like 2016Q1 as 31-03-2016, 2016Q2 as 30-06-2016, and so on. So I want this to be dynamic for next year use. I am an excel user and want to shift my model to alteryx. In excel, I used to define my start date as 2016Q1 and use a function of eomonth(startdate,3) to get all these dates till 2020Q1.

Next year this will shift one year forward (from 2017Q1 to 2021Q1) so I just want to change the start date to 2017Q1 and want it to change my date variables one year forward.

Or can I upload an excel table with dates and variable names which then will be used in the formula in alteryx workflow?

I can provide you a screenshot if it is not clear.

Accepted answers

MarqueeCrew

@tech_enthusiast ,

If you setup an input like this, you can use a multi field formula to group by the quarter and add a descending year prefix to each date. That would dynamically create your input.

DATE. Quarter

-03-31. 1

-03-31

-06-30

....

-12-31. 12

cheers,

mark

All comments

MarqueeCrew

@tech_enthusiast ,

If you setup an input like this, you can use a multi field formula to group by the quarter and add a descending year prefix to each date. That would dynamically create your input.

DATE. Quarter

-03-31. 1

-03-31

-06-30

....

-12-31. 12

cheers,

mark

tech_enthusiast

Hey @Mark,

Thank you for your quick response.

I understand your solution, but I don't practically know how can I set up this input if you can explain a bit more. One more thing is I have saved these dates as global constants and used them in my formula multiple times. Can these constant be changed using some formula?

Since I need to know the till date position, each calculated previous Qtr position is accumulated or added to the new column. Is there a way I can add back the previous 10 or 20 column values to this column.

Thanks

pankajk

There are multiple ways to address this situation. In my prior role in Finance, we used to do this all the time when monthly or quarterly reporting were required. Happy to chat offline as well to talk about different solutions.

tech_enthusiast

Mark,

It was a great pleasure talking to you. I really learnt so much from you in this short time.

Can you please recommend some tutorial which could help me in learning these tools that you know.

I once again appreciate your time and continuous efforts you put in.

My good wishes to you and your family. 🙂

Regards,

Abdullah

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335