3 Replies Latest reply: May 14, 2017 6:14 PM by Stefan Wühl RSS

    How do I write a nested if then statement for count?

    Ricky Scheiber

      Currently I have a rather manual process to ultimately determine the count of a KPI under specific conditions, which works for only two fields (in this case, Master Retarget and Master Age Band):

       

      count(distinct(if([Master Retarget]='Yes' AND [Master Age Band]='Due > 90 Days',[Master ID])))

      +

      count(distinct(if([Master Retarget]='Yes' AND [Master Age Band]='Due 31 - 60 Days',[Master ID])))

      +

      count(distinct(if([Master Retarget]='Yes' AND [Master Age Band]='Due 0 - 30 Days',[Master ID])))

      +

      count(distinct(if([Master Retarget]='Yes' AND [Master Age Band]='Due 61 - 90 Days',[Master ID])))

       

      I want to add a third field [Issue Source] under which I only want the count for 'Regulation' and 'Testing' named items to appear in the final Count.

       

      Ultimately what I want to state is "X number of 'Regulation' and 'Testing' Master IDs were 'Retargeted' and 'Due' at this point in time. "

        • Re: How do I write a nested if then statement for count?
          Stefan Wühl

          Why do you need to use several aggregations? Could it happen that a Master ID appears in more than one Master Age Band and you need to aggregate the distinct count of all the bands?

           

          If not, I would assume that something like

          =count(distinct

              if([Master Retarget]='Yes' AND

                    Match([Master Age Band],'Due > 90 Days','Due 31 - 60 Days','Due 0 - 30 Days','Due 61 - 90 Days')

          ,[Master ID]))


          could do the same?


          And how does your data model look like?


          You could consider using set analysis to filter your records set:

          =Count(

          {<[Master Retarget] = {'Yes'},

          [Master Age Band] = {'Due > 90 Days','Due 31 - 60 Days','Due 0 - 30 Days','Due 61 - 90 Days'} >}

          DISTINCT [Master ID])


          If you need to add another condition, just add it like

          =Count(

          {<[Master Retarget] = {'Yes'},

          [Master Age Band] = {'Due > 90 Days','Due 31 - 60 Days','Due 0 - 30 Days','Due 61 - 90 Days'},

          [Issue Source] = {'Regulation','Testing'} >}

          DISTINCT [Master ID])