Use dynamic header in the formula tool

[Paddi]

Hi all,

I have just figured out how to create a dynamic header, but I need to further do some calculation. Do you have any idea about how can I use data under dynamic header for calculation?

The value of Change by product weekly should be [20221122_Grand Total]- [20221115_Grand Total], but the date in the header changes every week.

I tried to write the formula in this way: [DateTimeformat(DatetimeAdd(ToDate(DatetimeNow()),-3,"days"), '%Y%m%d')+'_'+'Grand total']-[DateTimeformat(DatetimeAdd(ToDate(DatetimeNow()),-10,"days"), '%Y%m%d')+'_'+'Grand total'], but it does not work.

Can you kindly help me with it. The situation has been attached. Thank you so much.

In excel calculation, it should be quite simple.

In Alteryx, the header can be dynamic using this expression.

[ShankerV]

Hi @Paddi 

Please find the expected output,

Many thanks

Shanker V

[ShankerV]

@Paddi 

Use the below formula,

datetimeformat(dateadd(datetimenow(),-10,"days"),'%Y-%m-%d')+'_'+'Grand total'

Kindly accept this solution if it provided a solution to your question.

Many thanks

Shanker V

[Paddi]

Hi @ShankerV , thank you for your solution. But I have already figured out how to make the header dynamic. I'm struggling with the next step. Do [20221122_Grand total] minus [20221115_Grand total] by Using the formula tool. And the date info in the header changes dynamically every week. Do you know what to do with this? Thank you.

[binuacs]

@Paddi One way of doing this with the help of dynamic select tool

[Paddi]

hihi @binuacs Really appreciate your help, but I cannot open the file you uploaded since it is in a .yxzp format, can you re-upload a .yxzb version? Thank you so much.

[binuacs]

@Paddi uploaded the workflow. The input file I used as an excel file

[OllieClarke · Accepted solution]

Hi @Paddi 

Here's one way of doing it:

using the dynamic rename tool, we can rename based on formula, so we can dynamically rename the relevant columns to first and second.

Then we can simply write our normal formula and reverse the change.

I don't think @binuacs's solution will work here, as the names of the columns are referenced in the formula tool, and they will change...

Hope that helps,

Ollie

[binuacs]

@OllieClarke @Paddi mentioned in his question that he was able to figure out how to create the dynamic header and was looking for the dynamic calculation, that's why I only include the dynamic calculation in the workflow 

[OllieClarke]

@binuacs right, but your calculation isn't dynamic. When the workflow is run tomorrow, 2 new fields will be brought through your select tool, neither of which are referenced in your formula tool. So it will error and not produce the expected output...