Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

garmittal
New Contributor II

Nested If using set analysis

  if(Year_Date = MaxYear,

      if(Sum([Total Services Charges]) > 100000,  sum((Num([Total Services Charges]/1000000,'$#,##0.00;($#,##0.00)'))) & ' M',

        if(Sum([Total Services Charges]) > 1000, sum(Num([Total Services Charges]/1000,'$#,##0.00;($#,##0.00)')) & ' K',

           sum(Num([Total Services Charges]/1000,'$#,##0.00;($#,##0.00)')) & ' K')

           )

           )

5 Replies
garmittal
New Contributor II

Re: Nested If using set analysis

Where MaxYear is my variable which i have created

rittermd
Honored Contributor

Re: Nested If using set analysis

What specifically is your question?

What do you want to do?

thiago_justen
Valued Contributor III

Re: Nested If using set analysis

Try this out:

if(Year_Date = $(MaxYear),

      if(Sum([Total Services Charges]) > 100000,  Num(sum([Total Services Charges]/1000000),'$#,##0.00;($#,##0.00)') & ' M',

        if(Sum([Total Services Charges]) > 1000, Num(sum([Total Services Charges]/1000),'$#,##0.00;($#,##0.00)') & ' K',

           Num(sum([Total Services Charges]/1000),'$#,##0.00;($#,##0.00)') & ' K')

           )

     )

Thiago Justen Teixeira Gonçalves
WhatsApp: 24 98152-1675
Skype: justen.thiago
garmittal
New Contributor II

Re: Nested If using set analysis

So what i am trying to achieve here is , I have historical data eg (2016 ,2017) But i want to display my visualization by latest year data . In order to achieve that i created a variable Max Year which store maximum year value . By comparing that variable with my field Year to date i want to do my calculations. , if i try writing it using Set analysis it will work . I need help to write my code using set analysis.

MVP
MVP

Re: Nested If using set analysis

Set Analysis is for more efficient filtering - its not a replacement for an If statement such as your expression. Your If is a branching If rather than a filter. You could kludge something together using a set expression but is unlikely to perform better and it certainly won't be simpler or more intuitive than a properly structured nested if.

I put the Num() outside the Sum() statements. It does nothing inside the Sum().

If(Year_Date = MaxYear,

    If(Sum([Total Services Charges]) > 100000, 

        Num(sum([Total Services Charges]) / 1000000) & ' M',

  

        If(Sum([Total Services Charges]) > 1000,

            Num(Sum([Total Services Charges]) / 1000) & ' K',

            Num(Sum([Total Services Charges]))

        )

    )

)

Community Browser