-
1. Re: Calculation of subgroup criteria
Mark FraserJan 21, 2016 4:26 AM (in response to paul.vanblokland)
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
-
197835.twbx 10.4 KB
-
-
2. Re: Calculation of subgroup criteria
paul.vanblokland Jan 21, 2016 4:32 AM (in response to Mark Fraser)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
Mark FraserJan 21, 2016 4:46 AM (in response to paul.vanblokland)
-
4. Re: Calculation of subgroup criteria
paul.vanblokland Jan 21, 2016 5:19 AM (in response to Mark Fraser)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
Mark FraserJan 21, 2016 5:35 AM (in response to paul.vanblokland)
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
-
197835.twbx 10.5 KB
-
-
6. Re: Calculation of subgroup criteria
paul.vanblokland Jan 21, 2016 5:49 AM (in response to Mark Fraser)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
Product in a store with X present in a store with X not present X 1 0 Y 2 3 Z 0 1 Hope this helps.
-
7. Re: Calculation of subgroup criteria
pooja.gandhi Jan 21, 2016 6:18 AM (in response to paul.vanblokland)1 of 1 people found this helpfulHi 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:
-
8. Re: Calculation of subgroup criteria
Mark FraserJan 21, 2016 6:36 AM (in response to paul.vanblokland)
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))
Assuming all is well, please mark Pooja's answer correct
Cheers
Mark
-
9. Re: Calculation of subgroup criteria
pooja.gandhi Jan 21, 2016 6:38 AM (in response to Mark Fraser)Oh yes! I forgot about the **** zeros
Good catch, Mark!
-
10. Re: Calculation of subgroup criteria
paul.vanblokland Jan 21, 2016 7:03 AM (in response to pooja.gandhi)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 FraserJan 21, 2016 7:10 AM (in response to paul.vanblokland)
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
-
197835.twbx 10.6 KB
-
-
12. Re: Calculation of subgroup criteria
pooja.gandhi Jan 21, 2016 7:17 AM (in response to paul.vanblokland)1 of 1 people found this helpfulPaul,
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?
-
13. Re: Calculation of subgroup criteria
paul.vanblokland Jan 21, 2016 8:23 AM (in response to pooja.gandhi)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
pooja.gandhi Jan 21, 2016 8:44 AM (in response to paul.vanblokland)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?