
1. Re: Sum of count of dates by month from a cube
Jonathan Drummey Oct 23, 2012 8:26 PM (in response to mohammad.alrawi)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

distinct sum.twbx.zip 408.0 KB


2. Re: Sum of count of dates by month from a cube
mohammad.alrawi Oct 24, 2012 12:31 AM (in response to Jonathan Drummey)1 of 1 people found this helpfulThanks 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