topic Set Analyses with Nested If in App Development

Set Analyses with Nested If

Jan-Hendrik — Fri, 02 Apr 2021 08:28:41 GMT

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()

Re: Set Analyses with Nested If

agigliotti — Thu, 11 Jun 2020 13:29:56 GMT

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.

Re: Set Analyses with Nested If

treysmithdev — Thu, 11 Jun 2020 15:06:37 GMT

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.

Re: Set Analyses with Nested If

Jan-Hendrik — Fri, 12 Jun 2020 01:24:11 GMT

Awesome, thanks, that did the trick