Set Analyses with Nested If

[Jan-Hendrik]

Hi all'ya guru's

I'm trying to shorten the following set analyses statement:

if(vPeriod='YTD',
sum( {< yyyyFiscal={"$(vFiscalYYYY)"}, pppFiscal={"<=$(vFiscalPPP)"} >} ton ),
if(vPeriod='MTD',
sum( {< yyyyFiscal={"$(vFiscalYYYY)"}, pppFiscal={"$(vFiscalPPP)"} >} ton )
))

Have tried the following as an example, but not working:

sum({< yyyyFiscal={"$(vFiscalYYYY)"}, pppFiscal ={"$(=pick(match($(vPeriod),'YTD','MTD'),{"<=$(vFiscalPPP)"},{"$(vFiscalPPP)"}))" } >} ton )

Anyone have an idea of what the correct syntax should be or alternative function to use to bring it all under the same sum()

[agigliotti]

maybe this:

=sum( {< vPeriod = {'YTD'}, yyyyFiscal={"$(vFiscalYYYY)"}, pppFiscal={"<=$(vFiscalPPP)"} >} ton )
+
sum( {< vPeriod = {'MTD'}, yyyyFiscal={"$(vFiscalYYYY)"}, pppFiscal={"$(vFiscalPPP)"} >} ton )

replacing vPeriod with the field name.

I hope it can helps.

[treysmithdev · Accepted solution]

Usually, if the only difference in an expression is a single piece of text, you can leverage the variable to change the meaning.

In your example, the only difference is highlighted below in the two conditions:

if(vPeriod='YTD',
sum( {< yyyyFiscal={"$(vFiscalYYYY)"}, pppFiscal={"<=$(vFiscalPPP)"} >} ton ),
if(vPeriod='MTD',
sum( {< yyyyFiscal={"$(vFiscalYYYY)"}, pppFiscal={"$(vFiscalPPP)"} >} ton )
))

 I would change the whole expression to:

sum( {< yyyyFiscal={"$(vFiscalYYYY)"}, pppFiscal={"$(vPeriodCondition)$(vFiscalPPP)"} >} ton )

Where vPeriodCondition = '<=' for YTD or '' for MTD.

[Jan-Hendrik]

Awesome, thanks, that did the trick