Alteryx Designer Desktop Discussions

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

dynamic calculation based on dynamic columns name

vasudhajoshi
6 - Meteoroid

Hi Experts,

 

As the heading suggests. I need to create a dynamic calculation based on columns whose names will change - based on year and month like - 201912, 202001, etc.

 

I created a workflow using previous community answers but I am not able to alter the formula for the batch acc to my need.

for Column - [202001]

I need the formula to be - IIf([202001]-[201912]>0 And [201912]=0,[202001],0)

 

Then remove those columns and rename the column as 202001.

 

Right now, it is '<FormulaField expression="'+
'-'+[Concat_Name]
+'" field="SUM '+'-'+[Concat_Name]
+'" size="2" type="Int16" />'

 

Could anyone guide me on how to update the formula?

 

Attaching the sample workflow I have created till now.


Thanks

Vasudha

6 REPLIES 6
mjeckmans
7 - Meteor

Correct me if I'm wrong, but if I understand this statement correctly it only triggers true where 201912 = 0. Then going to the first part of the statement you will get 202001-201912 (which is 0) should be bigger than 0. So why not just check for whether 201912 = 0 and 202001 > 0?

And in this case, seeing as we're dealing with dates, you could first transpose, then sort by date if needed, and then use a multi-row formula to check both these instances. Then crosstab back if needed. That should make it dynamic enough.

 

Attached an example. It's not the prettiest, and it doesn't really give you values based on your input variables, but yeah... Hope at least the thought process helps 🙂🙂

mjeckmans
7 - Meteor

Correct me if I'm wrong, but if I understand this statement correctly it only triggers true where 201912 = 0. Then going to the first part of the statement you will get 202001-201912 (which is 0) should be bigger than 0. So why not just check for whether 201912 = 0 and 202001 > 0?

And in this case, seeing as we're dealing with dates, you could first transpose, then sort by date if needed, and then use a multi-row formula to check both these instances. Then crosstab back if needed. That should make it dynamic enough.

 

Attached an example. It's not the prettiest, and it doesn't really give you values based on your input variables, but yeah... Hope at least the thought process helps

mjeckmans
7 - Meteor

Correct me if I'm wrong, but if I understand this statement correctly it only triggers true where 201912 = 0. Then going to the first part of the statement you will get 202001-201912 (which is 0) should be bigger than 0. So why not just check for whether 201912 = 0 and 202001 > 0?

And in this case, seeing as we're dealing with dates, you could first transpose, then sort by date if needed, and then use a multi-row formula to check both these instances. Then crosstab back if needed. That should make it dynamic enough.

 

Attached an example. It's not the prettiest, and it doesn't really give you values based on your input variables, but yeah... Hope at least the thought process helps

 

 

(If I doubleposted this... I'm having TONS of issues with the community site today)

vasudhajoshi
6 - Meteoroid
there is one problem since there are multiple such columns. I am facing the
below issue. My formula is skipping a row.
[image: image.png]
mjeckmans
7 - Meteor

I'm sorry. In your original flow there's 1 tool that doesn't show up for me, and your image I can't see either unfortunately.

vasudhajoshi
6 - Meteoroid

@

 

Labels