Qlik Community

Qlik Sense Advanced Authoring

Discussion board where members can learn more about Qlik Sense Advanced Authoring.

Announcements
See why BI users voted Qlik #1 in 11 categories. GET REPORT
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

Set Analyses with Nested If

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

 

Labels (1)
1 Solution

Accepted Solutions
Highlighted
Luminary
Luminary

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.

Blog: WhereClause   Twitter: @treysmithdev

View solution in original post

3 Replies
Partner
Partner

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.

Highlighted
Luminary
Luminary

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.

Blog: WhereClause   Twitter: @treysmithdev

View solution in original post

Highlighted
Partner
Partner

Awesome, thanks, that did the trick