
1. Re: Calculate percent of total when using a measure defined as WINDOW_SUM
Joe Oppelt Oct 7, 2016 9:50 AM (in response to Francesca Cristoforetti)[Customer Bands] / [your window_sum_calc]

2. Re: Calculate percent of total when using a measure defined as WINDOW_SUM
Francesca Cristoforetti Oct 7, 2016 11:15 AM (in response to Joe Oppelt)Sorry, I don't understand what you mean. [Customer Bands] is a discrete field which assume values L, M or H. My window sum calculation is called [Running Customer Count] and it obsiously takes numeric integer values.

3. Re: Calculate percent of total when using a measure defined as WINDOW_SUM
Joe Oppelt Oct 7, 2016 12:18 PM (in response to Francesca Cristoforetti)Oh. I thought [Customer Bands] was the calc that was putting out 6, 9, 22 for November 2011.
So whatever field you are using to display the values. That's what you want in the numerator.
SUM([Value Field]) / [your window_sum_calc]

4. Re: Calculate percent of total when using a measure defined as WINDOW_SUM
Francesca Cristoforetti Oct 8, 2016 12:55 AM (in response to Joe Oppelt)I have attacched a woorkbook in order to better explain my situation. As you can see in Sheet1 I have calculated for each month che distinct customer count in each group (L, M or H) and the percent of total for each row. I would like to do the same with running count (Running Customer Count field) and I still don't know how do this. Sorry, but I don't unterstand how apply what you write in your answer, Joe.
Now I have also understand that I did an error in defining my Customer Bands. In my idea the groups are based on the values of two metrics: order count and average order amout for each customer. I would like to define also this measures as cumulated values, from the beginning to the current month, but I can't understand how I should do. In my workbook I have defined them at fixed level of detail ([customer_id]), which means that I'm using the current classification of my customers, including all orders. the two field I'm currently using are Average Amount (customer level) and Order Count (customer level).
I have tried something like this:
{ FIXED [customer_id] : RUNNING_SUM( COUNTD([order_id]) ) }
and
{ FIXED [customer_id] : RUNNING_SUM( SUM([amount]) ) }
But an error says that "Level of detail expression cannot contain table calculation or the ATTR function"
As you can see in Sheet2, the customer band associated with a customer doesn't change in time because it's the actual classification.
Any other ideas?
PS: should I open another thread for this second question?

Running count distinct.twbx 942.7 KB


5. Re: Calculate percent of total when using a measure defined as WINDOW_SUM
Joe Oppelt Oct 10, 2016 7:09 AM (in response to Francesca Cristoforetti)Let's take one step at a time.
You have three Bands: H, L, M. Are the Running sums correct? Even before that, arte the Running Customer Count and Distinct Count of Customer ID correct?
There is an inherent problem with trying to do a running COUNTD. If you go monthbymonth, your monthly COUNTD will get the distinct IDs within that month. A running COUNTD will just sum up the monthbymonth totals. But if a customer bought something in January and also bought something in March, he will be counted in the COUNTD for each of those months, and therefore he will be counted twice in the running total. Maybe you want it to be counted that way, but at least be aware of this concern. (And there is not an easy way to address that in Tableau.)
Once we have the various counts correct, then we can work at creating percentages. Can you clarify that the numbers on Sheet 1 are correct?

6. Re: Calculate percent of total when using a measure defined as WINDOW_SUM
Francesca Cristoforetti Oct 11, 2016 12:04 AM (in response to Joe Oppelt)Hi Joe
using this calculated field
WINDOW_SUM(
COUNTD(
IF [date]={FIXED [customer_id] : MIN([date])}
THEN [customer_id]
END),FIRST(),0)
I'm counting each customer just on his first order, from the beginning until the current month. te calculated field seems to be right.
My problem now is how shoul I calculate the three bands, as I explained in my previuos post (is it clear?) and then how should I calculate the percentage.

7. Re: Calculate percent of total when using a measure defined as WINDOW_SUM
Joe Oppelt Oct 11, 2016 7:52 AM (in response to Francesca Cristoforetti)See attached.
I made an extra table calc that sums up the total. I didn't realize that the total you are showing on your sheet was tableaugenerated. So make your own total, and then do the math. Note that the table calc for the total runs along [Bands] and the percentage calc runs along months.

Running count distinct A.twbx 931.4 KB


8. Re: Calculate percent of total when using a measure defined as WINDOW_SUM
Francesca Cristoforetti Oct 14, 2016 11:33 AM (in response to Joe Oppelt)Thank you for your answer Joe, It seems to be what I was looking for.
Now I'm going to solve my problem about the calculation of [Customer Bands].