5 Replies Latest reply on Oct 21, 2017 8:59 AM by Branden Kornell

# Count # of Stores When Calculated Field Value <0 ?

hi all:

I'm trying to count how many stores have a '% Change' less than 0% - for my example below, my expected answer is a count of 1.

I'm expecting to create some form of a COUNT calculated field that takes into consideration the condition that the % Change is <0.

I have a few columns of data:

Store Name
TY Sales
LY Sales
% Change - TY/LY
A10090.11
B10090.11
C8090-.11

My end goal would be to have a calculated field that would look like this when added to my shelf:

Count of Stores with Negative Change
1

I'm not really sure where to start - in SAP BusinessObjects it might look something like this - but this is not a valid function in Tableau:

count([Store Name] WHERE [%Change - TY/LY] < 0)

I appreciate any insight you guys might have!

• ###### 1. Re: Count # of Stores When Calculated Field Value <0 ?

Hi Christopher. Can you post an Excel file with a sample of your underlying data? The method to arrive at your answer depends on how your data is structured and how you have calculated % change.

• ###### 2. Re: Count # of Stores When Calculated Field Value <0 ?

Hi

Ok - have 2 questions here

the first is caclulating the % diff YOY and that is done with this

The second is counting customers <0

Baed on the  Superstore example attached they return this

Note I just used Customer Name and filtered a small set

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 3. Re: Count # of Stores When Calculated Field Value <0 ?

If this is the grain of your data (one row per store), then the simplest solution is two computed items:

[% Change]: [TY Sales] / [LY Sales] - 1

[Negative Change Stores]:

if [% Change] < 0 then

1

else

0

END

You can then drag [Negative Change Store] into the Viz, and it will count correctly whether you are looking at individual stores

Or across stores (Remove [Store Name] from Rows

If there is more than one row per store, you would need to use a different formula with COUNTD (to remove duplicates).

1 of 1 people found this helpful
• ###### 4. Re: Count # of Stores When Calculated Field Value <0 ?

This did it - thanks so much!

• ###### 5. Re: Count # of Stores When Calculated Field Value <0 ?

Great! Can you please mark the message as the correct answer, if appropriate?

Then it will appear as answered on the message boards, and help me build up my reputation.

Good luck with your work!