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

# Sum of count of dates by month from a cube

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

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

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