Use dynamic header in the formula tool

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.

date.xlsx

Datasets

Dynamic Processing

Expression

Accepted answers

OllieClarke

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 @binu_acs'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

dynamic.yxmd

All comments

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.

binu_acs

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

Dynamic_Selet_1125.yxzp

Paddi

hihi @binu_acs 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.

binu_acs

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

Dynamic_Selet_1125.yxmd

OllieClarke

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 @binu_acs'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

dynamic.yxmd

binu_acs

@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

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

flying008

Hi, @Paddi

If you already got dynamic name of fields [2022MMdd_Grand total] , then come with the workaround of @OllieClarke .

But if you don't and have more ideas, maybe can follow me:

录制_2022_11_26_15_28_08_269.gif

Paddi

hihi @binu_acs Your solution looks good but didn't actually solve my pain point in the workflow. Thank your for your help anyway~~

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703