1 2 Previous Next 15 Replies Latest reply on Jan 21, 2016 9:03 AM by paul.vanblokland

# Calculation of subgroup criteria

Hi all,

I need a calculation with checks a criteria in a subset.

I have the following dataset:

StoreId
Product
1x
1y
1y
2y
2y
2z
3y

I want to know how many times y occurs in a store where also X occurs. And how many times Y occurs where X does not occur.

The result will look like this

Product
Times with XTimes without X
Y23

I think i have to look for a "level of detail"  expression. But i'm not sure.

Paul

• ###### 1. Re: Calculation of subgroup criteria

Hi Paul

Is your example meant to reflect what you actually expect?

I only see 1 X entry for example?!

Something like this may work for you (its using LoD, you can tell by the use of { } )

X Count >> IF [Product]='x' THEN {FIXED [Store ID] : COUNTD([Number of Records])} ELSE NULL END

Non X Count >> IF [Product]<>'x' THEN {FIXED [Store ID] : COUNTD([Number of Records])} ELSE NULL END

Example attached, v9.1

Cheers

Mark

• ###### 2. Re: Calculation of subgroup criteria

Hi Mark,

Almost i think but its about the Y count. Y appears 2 times in a store where X is present.

• ###### 3. Re: Calculation of subgroup criteria

Sorry Paul, it's me, but I don't quite understand

... StoreID 1- If an X is present, then count only 1 X, and 0 non X? So it should look like this?

Total are

X Count =  1

Non X count = 5

Or something different?!

Cheers

Mark

• ###### 4. Re: Calculation of subgroup criteria

It's a bit confusing i know, but thanks for your help so far

I want to know how many times Y appears in a store which has one or more X in it.

So in my example store number 1 is the only store which has product X. So now I count the Y in store 1, Y appears 2 times in store 1.

• ###### 5. Re: Calculation of subgroup criteria

OK, here with attempt 2...

You can display the X Count or not, but we do need it calculated as its used in the non-X count.

X Count >> IF [Product]='x' THEN {FIXED [Store ID] : COUNTD([Number of Records])} ELSE NULL END

+

Non X Count >> IF [Product]='x' THEN {FIXED [Store ID]: SUM([Number of Records])-SUM([X Count])} ELSE 0 END

v9.1 attached.

Cheers

Mark

• ###### 6. Re: Calculation of subgroup criteria

Hi Mark i think we're not in sync Sorry if i didn't explain it clear enough.

I'm looking for the number of times Y appears in stores where 1 or more X is present. So X is just a criteria not thing i want to count.

The complete result table would look like this

Productin a store with X presentin a store with X not present
X10
Y23
Z01

Hope this helps.

• ###### 7. Re: Calculation of subgroup criteria

Hi Paul!

That would be a single calculation like this:

{FIXED [Store ID]: MAX(IF [Product] = 'x' then 'with x' else 'not with x' END )}

Drag this calculation on columns, product on rows and number of records on text:

Result:

1 of 1 people found this helpful
• ###### 8. Re: Calculation of subgroup criteria

We'll just wait till Pooja turns up... oh wait...!

Great work Pooja Gandhi

To try and salvage something from this...

If by any chance you have to show 0 rather than leave them blank, swap Number of Records for this new calculated field > ZN(LOOKUP(SUM([Number of Records]),0))

Cheers

Mark

• ###### 9. Re: Calculation of subgroup criteria

Oh yes! I forgot about the **** zeros

Good catch, Mark!

• ###### 10. Re: Calculation of subgroup criteria

Thanks Pooja Gandhi ! Can you explain why you use MAX?

Mark Fraser can you attach me the work book so i can see what you mean with the zeros?

Thanks for help guys!

• ###### 11. Re: Calculation of subgroup criteria

Mark Fraser can you attach me the work book so i can see what you mean with the zeros?

of course, please find attached v9.1

• ###### 12. Re: Calculation of subgroup criteria

Paul,

While using LOD expressions, the result need to be an aggregate. You could use aggregation as it relates to the result within the IF statement. Here the result was a string data type because we stated 'with x' and 'not with x' and hence used MAX().

So that tells Tableau that at a FIXED store level, if there are products with 'x' then show 'with x' for all products within that store. If you didn't use LOD expressions and instead used a general IF statement, 'with x' would be the output for only those rows where product is a 'x'.

Something like below, calculation 2 would've shown 'with x' only for the row where product is 'x'. But since you wanted to count the records for all products and not just product 'x', we used LOD versus using the general IF statement.

Makes sense?

1 of 1 people found this helpful
• ###### 13. Re: Calculation of subgroup criteria

Thanks for reply. It became a little bit more clear Still having hard time understanding how the MAX works in this context. I associate MAX with the fetching the biggest number in a collection since the if statement returns a string it feels strange.

• ###### 14. Re: Calculation of subgroup criteria

Ok lets look at it a little differently. Lets change the formula to:

Calculation1: IF [Product] = 'x' then 1 else 0 end

This will throw a 1 for all rows where product = 'x' so it looks like this:

Notice there is a 0 for the row where product = 'y'.

Calculation2: { fixed [Store ID] : MAX([Calculation1 ]) } so it looks like this:

MAX of calculation 1 at a fixed store level is 1 for store id 1. So it will fill all rows for that store with a 1. You need a 1 for the row where product = 'y' because you are counting the number of times all products that appear where product = 'x'.

So same concept with the text 'with x' and 'not with x'. In the example above, 1 = with x and 0 = 'not with x'. and MAX() for the strings in LOD would work the same way. Does that make sense now?

1 2 Previous Next