6 Replies Latest reply on Jan 31, 2019 12:08 PM by Prateek Panigrahy

    Get Distinct Count

    Prateek Panigrahy

      Hi All,

       

      I have a very unique requirement. I have an auction data something like this

       

      Untitled.png

       

      I have to get summary counts(Distinct ID) for Auction_Flag,New_Flag,Recycled_Flag. So the summary would look something like this

                                   Untitled.png

       

      The condition is based on the Auction_End_Dt selected by the user, the counts should be distinct ID in the date range for BTS,New and Recycled. So, if the ID is considered in New_Cnt once for that date range then you have consider it a 0 for Recycled even though we have Recycled auctions during the time frame. However, if there is no New_Flag for an asset during the time frame, then we should consider it for the Recycled_Cnt. So the Total Auction_Cnt=Total  New_Cnt+ Total Recycled_Cnt.

       

      I would really appreciate for suggestions and ideas.

       

      Thanks,

      Prateek

        • 1. Re: Get Distinct Count
          Deepak Rai

          Please attach some workbook

          • 2. Re: Get Distinct Count
            Prateek Panigrahy

            I have share the Snapshot of the everything. In case you need the source data use this

             

            select 1 as [ID], 1 as [Auction_ID],1 as Auction_Flag,1 as [New_Flag],NULL as [Recycled_Flag],'2019-01-01' as [Auction_End_Dt]

            Union

            select 1 as [ID], 2 as [Auction_ID],1 as Auction_Flag,NULL as [New_Flag],1 as [Recycled_Flag],'2019-01-10' as [Auction_End_Dt]

            Union

            select 1 as [ID], 4 as [Auction_ID],1 as Auction_Flag,NULL as [New_Flag],1 as [Recycled_Flag],'2019-01-21' as [Auction_End_Dt]

            Union

            select 1 as [ID], 5 as [Auction_ID],1 as Auction_Flag,NULL as [New_Flag],1 as [Recycled_Flag],'2019-02-10' as [Auction_End_Dt]

            Union

            select 1 as [ID], 6 as [Auction_ID],1 as Auction_Flag,NULL as [New_Flag],1 as [Recycled_Flag],'2019-02-20' as [Auction_End_Dt]

            Union

            select 2 as [ID], 3 as [Auction_ID],1 as Auction_Flag,1 as [New_Flag],NULL as [Recycled_Flag],'2019-01-20' as [Auction_End_Dt]

            Union

            select 3 as [ID], 7 as [Auction_ID],1 as Auction_Flag,NULL as [New_Flag],1 as [Recycled_Flag],'2019-02-21' as [Auction_End_Dt]

            • 3. Re: Get Distinct Count
              Deepak Rai

              This does not help..Herein Forum, we need some data to help.

              • 4. Re: Get Distinct Count
                Prateek Panigrahy

                I cannot share the live data because of the company policy. I just shared the closest sample data to the actual data. let me know what you need?? a sample workbook with this sample data?

                • 5. Re: Get Distinct Count
                  Deepak Rai

                  Just fake some data to give you logic for application to your actual content.

                  • 6. Re: Get Distinct Count
                    Prateek Panigrahy

                    Ok I have attached the dashboard. Basically based on the user selection of the date it should show the three summary counts. The counts for each summary is for distinct item id and the total Auction Cnt=Total New Cnt +Total Recycled Cnt.

                    Let me know if you need any additional info