Alteryx Designer

Find answers, ask questions, and share expertise about Alteryx Designer.
Register for the upcoming Live Community Q&A Session - and don't forget to submit your questions for @DeanS regarding the future role of analytics here.
SOLVED

Need help with multiple calculation on dynamic columns

Highlighted
6 - Meteoroid

Hi All,

 

I have been stuck at some calculations I need to do with columns based on "mmyyyy" combination and keep on updating. So the calculation needs to be dynamic to handle the column header change.

I need to do different calculations. I was able to solve it when the dynamic columns were similar(mmyyyy format). Attaching the sample workflow for your reference

But now there is another column which needs to be handled ( format - "Licmmyyyy")

 

Calculations are as below -

1. "Upgrade" AS Metric
,IIf([202001]-[201912]>0 And [201912]<>0 And [Lic202001]>[Lic201912],[202001]-[201912],0) AS 202001
2. "Pricing" AS Metric
,IIf([202001]-[201912]>0 And [201912]<>0 And [Lic202001]<=[Lic201912],[202001]-[201912],0) AS 202001

 

Any help on how to approach this would be really helpful.

 

Thanks 

Vasudha

Highlighted
12 - Quasar

Hi, @vasudhajoshi 

 

I'm not certain I'm tracking.

If 202001 is a column, then you now might have too many parameters to use the IIF approach without nesting complexity.

Perhaps use IF THEN ELSEIF ELSE ENDIF per below in the Formula Tool?

 

column = 202001

IF [Metric] = 'Upgrade' And

[202001]-[201912]>0 And

[201912]<>0 And

[Lic202001]>[Lic201912]

THEN [202001]-[201912]

 

ELSEIF [Metric] ='Pricing' And

[202001]-[201912]>0 And

[201912]<>0 And

[Lic202001]<=[Lic201912]

THEN [202001]-[201912]

 

ELSE 0

ENDIF

Highlighted
8 - Asteroid

Hi @vasudhajoshi ,

Well, this was very challenging indeed. I drew up a workflow basis my understanding of your need. Let me know if this works for you.

 

Regards

Benn

 

P.S: In case this solves your query, kindly mark this as solution.

 

Highlighted
6 - Meteoroid

Thanks Benn! This works for me. I learned new tools as well (Imputation).

Highlighted
6 - Meteoroid

@RobertOdera Thanks for your reply. The column names will keep on updating that's why I couldn't use the IF ELSE login you mentioned.

Highlighted
12 - Quasar

Sure thing, @vasudhajoshi 

Glad you got a working solution!

Awesome approach @Bennel_Wilson !

Highlighted
8 - Asteroid

thanks @RobertOdera 

Labels