5 Replies Latest reply on Jun 9, 2017 4:20 AM by Tracey Muchugu

How to Count the number of Stores with Profit <0 and no reason why

Hi, wondering is someone can help with how to go about creating the following 2 measures:-

1.  Count of negative stores

- count the Store if the YTD Profit <0 and the month chosen (by a parameter) does not have a reason)

- if the "individual" month/year chosen has a reason, then don't count

2.  % of negative stores

- distinct count of all stores that have Profit YTD <>0  /  count of negative stores

Ultimately, depending on the month/year parameter chosen, the count of negative profit stores will reflect if the YTD Profit <0 and where there is NO reason in that particular month.

Example here, but also attached tableau extract with some data.

Store A    Profit Month     Profit YTD    Reason                Count

Jan           \$2,000             \$2,000          Null                       No

Feb           -\$1,500             \$500           Goods stolen        No

Mar           -\$600               -\$100           Goods stolen        No

Apr            -\$1,000           -\$1,100        Null                        Yes

May          \$300                -\$800           Null                        Yes

Resulting output required is only 3 lines (and is linked to the month/year parameter chosen) - example being:

Count all Stores                                       872

Count Stores with Negative Profit               8

% Stores with Negative Profit                 0.92%

What I'd like to do is on Measure - Neg SP Count:   if sum([Profit YTD]) <0 AND ISNULL([Reason1](parameter month/year selected)) THEN 1 else 0 END

Appreciate any assistance.

Tracey

• 1. Re: How to Count the number of Stores with Profit <0 and no reason why

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 me--did 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

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 & non-aggregate 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

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

1 of 1 people found this helpful
• 4. Re: How to Count the number of Stores with Profit <0 and no reason why

Hi Walt, absolutely brilliant thank you !!   No way I would have figured that out.  Did have an issue with the running sum going across years (as it needs to be YTD profit), so made the following small change to the "Negative Profit Calculation".

So Happy

• 5. Re: How to Count the number of Stores with Profit <0 and no reason why

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