2 Replies Latest reply on Oct 24, 2012 12:31 AM by mohammad.alrawi

    Sum of count of dates by month from a cube

    mohammad.alrawi

      Hi,

      I am using tableau with an MS cube.

      I have count distinct number of customers per day like:

      Date              Unique Customers

      10/1/2012     10    

      10/2/2012     15

      10/3/2012     25

       

      I would like to sum the total of the daily distinct customers per month (without showing the days) like:

       

      October 2012     50

       

      if I drag the month to the row shelve then I get the distinct customers per month.

      October 2012     30 < this the distinct number of customers in those 3 dates listed above.

       

      If I drag the days and select totals (sum) then I get the correct number but it is showing like this :

       

      10/1/2012     10    

      10/2/2012     15

      10/3/2012     25

      Grand Total  50

       

      Any help?

        • 1. Re: Sum of count of dates by month from a cube
          Jonathan Drummey

          Hi,

           

          What you need to do is have the Day of the date on the Level of Detail shelf (to get all the days of the month), then the Month of the date on the Rows shelf, and then use a table calculation to sum over the distinct count, like this formula:

           

          IF FIRST()==0 THEN

              WINDOW_SUM(COUNTD([Customer]),0, IIF(FIRST()==0,LAST(),0))

          END

           

          The IF and IIF statements are used to reduce the number of rows returned and speed up the computation, the key bit is the WINDOW_SUM.

           

          I set this up in the attached using the Superstore Sales data.

           

          Jonathan

          • 2. Re: Sum of count of dates by month from a cube
            mohammad.alrawi

            Thanks Jonathan!

             

            I was able to solve that as well with a slightly different way - but using window_sum was the key as you have mentioned.

             

            Placed:

            Date in row shelf.

            Month in row shelf

            running sum of customers.

            created a calculated field of running_sum/window_sum ratio value and placed it in filter shelf (table calculation per pane) and set filter to equal 1

            Hide Date header.

             

            Thanks again!

            Mohamed

            1 of 1 people found this helpful