5 Replies Latest reply: Mar 28, 2018 1:15 AM by Jonathan Dienst RSS

    Nested If using set analysis

    Garima Mittal

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

                 )

                 )

        • Re: Nested If using set analysis
          Garima Mittal

          Where MaxYear is my variable which i have created

          • Re: Nested If using set analysis
            Mark Ritter

            What specifically is your question?

            What do you want to do?

              • Re: Nested If using set analysis
                Garima Mittal

                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.

                  • Re: Nested If using set analysis
                    Jonathan Dienst

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

                            )

                        )

                    )

                • Re: Nested If using set analysis
                  Thiago Justen Teixeira

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

                             )

                       )