Help regarding Alteryx constants and variables

[tech_enthusiast]

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.

[MarqueeCrew · Accepted solution]

@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.      1

-03-31

-03-31

-03-31

-06-30

....

-12-31.     12

-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

[MarqueeCrew]

Sorry but constants don't change. I'll send you a PM and I can speak with you directly about your request. We can make a YouTube video and show everyone how to dynamically create your quarters. 

cheers,

mark

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

Hello Mark,

Your multifield formula clicked me and I am thinking to create a table containing all these dates but there are two possible scenarios in this and I need your help regarding this.

The table formed will be like this

Y1Q1                    Y1Q2                 Y1Q3              ..................

31-03-2016          30-06-2016         30-09-2016    ..................

1) Can I use Y1Q1 value (31-03-2016) as a constant for all values in my column? Just like we fix in excel as $A$1 and it remains the same throughout the column. 

2) Or do I have to create a full column Y1Q1 (31-03-2016) with dimension as 1 times the number of rows the data it is to be applied for?

I would really appreciate your help on this.

Regards,

Abdullah

[pankajk]

Hi Abdullah - you can use workflow user constants and they will act like a constant in the workflow

https://help.alteryx.com/current/designer/constants

Alternate way is to create table and bring it in like you mentioned.

[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