2 Replies Latest reply: Nov 11, 2016 5:44 AM by Divya Anand RSS

    Count of only distinct values from 3 formulas

    Divya Anand

      Can someone please help me with this issue?

       

       

      Formula 1:

      (Count({<Year=,Month=,Date=, Day=,TYPE={'A','B'},STATUS-={'CANCEL','REJECTED'},

       

      COMPLETED = {'>=$(=num(MonthStart(Max(Date1))))<$(=(Max(Date1)+1))'} >} distinct TICKETID)

       

      Formula 2:

       

      Count({<Year=,Month=,Date=, Day=,TYPE={'A','B'},STATUS-={'CANCEL','REJECTED'}, RATESTATUS={'R'},

       

      ASSIGNED = {'<$(=(Max(Date1)+1))'} >} distinct TICKETID)

       

      Formula 3:

       

      Count({<Year=,Month=,Date=, Day=,TYPE={'A','B'},STATUS-={'CANCEL','REJECTED'},

       

      ASSIGNED = {'>=$(=num(MonthStart(Max(Date1))))<$(=(Max(Date1)+1))'}, COMPLETED -= {'<$(=num(MonthStart(Max(Date1))))'} >} distinct TICKETID) )

       

      The output that I am expecting is a count of (Formula 1+Formula 2+Formula 3). However there could be situations where the TICKETID could be repeated in more than 1 Formula. And if I take a count as shown, then the duplicates are also considered in the count. Is it possible to get a count of distinct TICKETIDs only ?  

       

       

      I am using this expression (Formula 1+Formula 2+Formula 3) in a bar chart.

       

      For eg:

       

       

      The value that I would like to see on the bar chart is "9" & not "11".

       

      Any other information that is needed, Please let me know.

       

      Thank you.

        • Re: Count of only distinct values from 3 formulas
          popescu cosmina

          Hi,

          From your post i understand that F3 is not showing correctly,please change like this:

          Count({<Year=,Month=,Date=, Day=,TYPE={'A','B'},STATUS-={'CANCEL','REJECTED'},

           

          COMPLETED = {'>=$(=num(MonthStart(Max(Date1))))<$(=(Max(Date1)+1))'}

          +

          <Year=,Month=,Date=, Day=,TYPE={'A','B'},STATUS-={'CANCEL','REJECTED'}, RATESTATUS={'R'},

           

          ASSIGNED = {'<$(=(Max(Date1)+1))'} >

          +

          <Year=,Month=,Date=, Day=,TYPE={'A','B'},STATUS-={'CANCEL','REJECTED'},

           

          ASSIGNED = {'>=$(=num(MonthStart(Max(Date1))))<$(=(Max(Date1)+1))'}, COMPLETED -= {'<$(=num(MonthStart(Max(Date1))))'} >


          } distinct TICKETID)


          Practically, put count(<F1>+<F2>+<F3> distinct TICKETID).


          I hope this is what you need and works.


          Best regards,

          Cosmina