2 Replies Latest reply on Jan 14, 2016 7:59 PM by Manideep Bhattacharyya

    Find out Total Unique Number of Customer Plotted for Every Week

    Manideep Bhattacharyya

      In the Bar Graph I want to show monthly trend of Total Unique Customer billed for the Sub Category "Paper". Comparison done for two regions "Ontario" and "West". For that I have created a Calculated field called "Unique Customer" and from the Quick Table Calculations did a "running total" of that.

      First month is showing the right number. But I have problem from Second Month onward.

       

      Second month onward the formula simply showing SUM(Unique Customer Billed in first month + Unique Customer Billed in Second Month). But I want to show COUNTD (Customer Billed in first and Second Month) and so on for the following months. Third month should show COUNTD (Customer Billed in first, second and third Month) but the calculation is showing SUM(Unique Customer Billed in first month + Unique Customer Billed in Second Month + Unique Customer Billed in Third Month). If I get a workable solution for this would be of extreme help.

       

      Unique Customer Billed for Paper Trend.png

        • 1. Re: Find out Total Unique Number of Customer Plotted for Every Week
          Esther Aller

          Hi Manideep,

           

          Instead of using COUNTD() to count the unique number of customers, we know that each customer will only have one first date in the data set. Therefore, we can calculated the running total of the minimum date per customer:

           

          1. Create a calculated field with a name like "Minimum Date per Customer" with a calculation similar to the following:

            { FIXED [Customer Name] : MIN( [Order Date] ) }

            The above calculation uses the level of detail (LOD) expression FIXED to fix the first (or minimum) order date per customer.

          2. Create a calculated field with a name like "Running total of unique customers" with a calculation similar to the following:

            RUNNING_SUM( SUM(
            IF [Order Date] = [Minimum Date per Customer]
            THEN 1
            END
            ))

            The above calculation returns 1 when the date equals the minimum date per customer, which means each customer will only be counted once on their first order date.

           

          I hope this answers your question!

          1 of 1 people found this helpful
          • 2. Re: Find out Total Unique Number of Customer Plotted for Every Week
            Manideep Bhattacharyya

            Right Answer.JPG

            Dear ESTHER,

             

            I have almost got the answer from your hint. I just did a little change here:

             

            Created a Calculated field called "Launched Date" with the following calculation:

            {fixed [Customer ID]: MIN([Order Date])}

             

            and then created another field "Total Customer Till Date" with the following calculation:

             

            COUNTD(if DATEDIFF('day',[Launch Date],[Order Date])=0

                                then [Customer ID]

                                END)

             

            The above calculation return the count of unique customer set for their first order.

             

            and then drag that field into Rows shelf and from Quick Table calculation changes to "Running Total".

            A small word of caution:

            Since LOD expression executes before filters, so if at all any filters to be added in the sheet, then its has to be added in the Data Source or in the Context Filter. and Customer ID should be unique in the dataset.

            You made my day. Cheers !

             

            Thanks,

            Manideep