
1. Re: How to Count the number of Stores with Profit <0 and no reason why
Walt Reed Apr 24, 2017 8:06 AM (in response to Tracey Muchugu)Hi Tracey,
I think I'm understanding your problem, but if not, let me know.
1. To create "Count of Negative Stores":
2. I was a little confused on this due to your wording. Your original post said to do a distinct count of stores with Profit YTD <> 0 (not equal to), but I assumed you meant less than 0. So this formula does that, then divides by the "Count of Negative Stores":
One more thing that confused medid you want to factor in the Parameter Month/Year for the 2nd calculation?
Regardless, to get your desired output, Count all stores = COUNTD([Store Desc])
Count stores with negative profit = [Count of Negative Stores]
% Stores with Negative Profit = [% of Negative Stores]
You have an older version of Tableau, so unfortunately I can't attach the workbook I modified.
Walt

2. Re: How to Count the number of Stores with Profit <0 and no reason why
Tracey Muchugu Apr 25, 2017 6:38 PM (in response to Walt Reed)Hi Walt, thank you very much for looking at this. I could have been clearer in my request so apologies.
Count of Negative Stores
When counting the stores with negative profit, the profit amount needs to be a running sum profit amount based on the selected Month/Year parameter.
The Profit YTD calculation in my workbook is monthly.
How can these aggregate & nonaggregate arguments work for this formula?
% Negative Stores
Yes the Month/Year should be factored in please. This calc also needs to take the running sum of the Profit based on the Month/Year selected.
Calc being:
based on Month/Year selected, COUNTD the Store Desc where the running sum of the Profit amount <> 0 (does not equal zero). This will give a distinct count of stores that have had  or + profit. then divide by the Count of Negative Stores.
In the workbook provided, Store A is a good example for what I need. Screenshot below. The running sum is positive so the Count should be 0.
Appreciate your time on this. Though the business is not yet at the latest version of Tableau I do have access to the latest Tableau desktop so all good to send back a file.
Regards
Tracey

3. Re: How to Count the number of Stores with Profit <0 and no reason why
Walt Reed Apr 26, 2017 7:51 AM (in response to Tracey Muchugu)Hi Tracey,
Thanks for the clarification, I think I follow what you want.
1. First, we want to calculate the YTD running profit based on the Parameter Month/Year, so the easiest way I found was to use a FIXED calculation:
2. Next, we want to count the negative stores, based on the above calculation, but also factoring in whether the Reason is blank or not (hence the 2nd FIXED calculation):
3. Next, we want to calculate the number of stores where sales <> 0, so we actually reference the first calculation we created:
4. Lastly, create the % of Negative Stores calculation, using the formulas from Step #2 and #3
I believe this works as you want it to. If it doesn't, reply back and let me know!
Walt
Neg Profit Stores.twbx 29.4 KB


5. Re: How to Count the number of Stores with Profit <0 and no reason why
Tracey Muchugu Jun 9, 2017 4:20 AM (in response to Walt Reed)Hi Walt, the solution you provided above is working brilliantly. I now need a way to count the negative stores over a (relative date) rolling last 12 month period, and wondered if you could help? This count of the negative stores
 shouldn't reference the month/year parameter ( I would apply a date filter 'relative' month for last 12 months).
 should take into account the running YTD sum of the Profit (within Year i.e.YTD running sum starts again for the next year).
Need to show the # negative stores by month (trend graph) for the last 12 months  so it will go across years.
I've put a screenshot below showing the output, but need it to go back into last year.
If you can assist, would this be enough information to proceed? or I can mock up a file to attach.
Kind regards
Tracey