Need help with multiple calculation on dynamic columns

[vasudhajoshi]

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

[RobertOdera]

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

[Bennel_Wilson · Accepted solution]

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.

[vasudhajoshi]

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

[vasudhajoshi]

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

[RobertOdera]

Sure thing, @vasudhajoshi 

Glad you got a working solution!

Awesome approach @Bennel_Wilson !

[Bennel_Wilson]

thanks @RobertOdera  🙂