14 Replies Latest reply: Apr 3, 2017 2:16 PM by Ananya Kushary RSS

    QlikSense

    Ananya Kushary

      I have a distinct count of records. When i include a second dimension(status), the distinct count includes the same record for 2 different status as 2 records(i would only like to consider it once, with the latest updated status).

      Please can anyone help with the calculation.

      Thanks

        • Re: QlikSense
          Devarasu R
          • Hi,


          Try like this,

          =Aggr(count(DISTINCT OrderID),status)


          3.JPG


          Thanks,

          Deva

          • Re: QlikSense
            Jayaseelan K

            Hi Ananya kushary,

             

                    Can you please post the example qvf, we will bring the output.

             

             

            Thanks,

            • Re: QlikSense
              Ananya Kushary

              Here is the data:

              Order No      Status                   Date

              1                  Complete             03/23/2017

              1                  Pending               03/22/2017

              2                  In Progress          03/25/2017

              2                  Pending               03/21/2017

              3                  Rejected              03/22/2017

              3                  In Progress          03/21/2017

               

              My bar graph with Order No should give me distinct count =3

              Also i want the graph to have a second dimension (Status of the max date). So graph should show:

              1- Complete, 1-in Progress, 1-Rejected

               

              Currently : it shows me distinct count 3, but when i include status it shows me 6 (which is incorrect).

              Hope this explains

                • Re: QlikSense
                  omar bensalem

                  Can you attach an image of what you curretly have (your bar chart) and you want to do with it?

                  • Re: QlikSense
                    omar bensalem

                    If I fully understand you, this is your source:

                     

                    load* , date(Date#(Date,'MM/DD/YYYY')) as DateStatus;

                    load * Inline [

                    Order No,      Status, Date

                    1,                  Complete,            03/23/2017

                    1,                Pending ,              03/22/2017

                    2,                  In Progress ,        03/25/2017

                    2,                  Pending,03/21/2017

                    3,                  Rejected ,            03/22/2017

                    3,                In Progress ,        03/21/2017

                    ];

                     

                    And this is the desired output?

                     

                    The Status bu Order No for the max Date?

                    Capture.PNG

                     

                    See the attached App:

                  • Re: QlikSense
                    Ananya Kushary

                    Yeah that's quite correct.

                    i have attached two screenshots of my bar chart

                    1)the first one has one dim (BU) and distinct count of order no (4)

                    2)the same chart when i add a second dimension (status) shows a distinct count of 6

                     

                    I would want only 4 to be displayed with the status of those 4 records (status of max date of "Update date")

                     

                    Dont know if this makes sense.

                      • Re: QlikSense
                        omar bensalem

                        Hi Ananya,

                         

                        What's this value LENEL and the dimension BU?

                        Can you alter your source table :

                        Order No      Status                   Date

                        1                  Complete             03/23/2017

                        1                  Pending               03/22/2017

                        2                  In Progress          03/25/2017

                        2                  Pending               03/21/2017

                        3                  Rejected              03/22/2017

                        3                  In Progress          03/21/2017

                         

                        And add the BU dimension?

                        So I can figure out what you're willing to do?

                         

                        And please don't forget to mark as helpful the answers that have been helpful to you (under action, button left of each message) and as correct the correct answer.

                         

                        Omar,

                      • Re: QlikSense
                        Ananya Kushary

                        Hi,

                        Thanks a ton for your help.

                        There seems to be some error when i tried this approach you suggested.

                        I tried for some more and this below formula worked for me

                         

                        Count(If(Aggr(NODISTINCT Max(Date), [Order No]) = Date, [Order No]))  as the measure formula