6 Replies Latest reply on Jun 30, 2018 4:10 AM by ANURAG BISHT

    IIF

    Gavin Wong

      Hi community

       

      can someone explain to me whats wrong here ?

      i am attempting to create a calculated field to show just 2017 sales, using iif

      but got this attached error...

       

      any help is appreciated.

        • 1. Re: IIF
          Tushar  More

          Hi,

           

          try this

           

          iif (year(order date)=2017, sales,0)

           

          ~Tushar

          1 of 1 people found this helpful
          • 2. Re: IIF
            Gavin Wong

            Thank you.

            • 3. Re: IIF
              Gavin Wong

              Hi Tushar  More

               

              see what i've done below, what does the error mean and how can i correct it ?

              Screen Shot 2018-06-30 at 12.29.10.png

              • 4. Re: IIF
                sudheer.kumar.5

                Hello Gavin,

                 

                How about the below calculation?

                 

                IF ATTR([Customer Name] = 'Aaron Bergman') THEN COUNTD([Order ID]) ELSE 0 END

                 

                 

                S

                • 5. Re: IIF
                  Tushar  More

                  Hi Gavin,

                   

                  you can use min or attr before customer name i.e.

                  min([customer name])='Aron'

                   

                  or attr ( [customer name ])

                   

                  ~Tushar

                   

                  If that helps plz mark the answer as correct and helpful.

                  1 of 1 people found this helpful
                  • 6. Re: IIF
                    ANURAG BISHT

                    Hi Gavin,

                    Considering an eg  data with 4 rows 2 columns     [name], [measure]:  abc,1    def, 2   ghi,3   jkl,4

                    Your IIF statement would work in 2 ways

                     

                    IIF([name]='abc', [measure],0)   creating green pill with some default aggregation lets say you specify it as COUNTD.

                     

                    OR

                     

                    IIF(attr([name])='abc',COUNTD([measure]),0) ,  this works because attr([name]) will check all rows containing abc as [name]

                     

                    In 1st case: if case checks row by row and then aggregates measure as final result, so the condition and the true criteria is non aggregated.

                     

                    In 2nd case: both the condition criteria and true condition are aggregated and result is a calculation with predefined aggregation type.

                     

                    Hope this helps