-
1. Re: Simple Formula Qns
Simon RuncNov 18, 2016 2:43 AM (in response to Angela.Koh)
hi Marcus,
So one way to aggregate the data at the level you want, while still retaining the "Row Level" computation of your bands, is to use an FIXED LoD
I've changed the UnschBreak Mins calculation to
[UnschBreak Mins]
IF [Reason Code]="UnscheduledEventsBreak" THEN {FIXED [Reason Code], [Date],[W2K]: SUM([Mins])} END
So the Mins are summed across Reason Code, Date and W2K (ignoring Team)...the "Cross Tab - Example" tab on the attached shows what this is doing. This then brings this element into that bucket. The issue now is that as you have equated it at row level, returning a 1 which is then SUMed...this SUMs to 2 and not 1 (as both rows, from each team for that person, contain a 1...as both rows qualify).
One way round this, is to not return a 1, but the Person (or whatever it is you want to count), else NULL, and then use a COUNTD on that field (NULLs don't get counted in a COUNTD, like 0 don't get added in a SUM)
Hope that helps and makes sense.
-
2. Re: Simple Formula Qns
Angela.Koh Nov 18, 2016 10:38 AM (in response to Simon Runc)Hi Simon! Thank you so much for your help... I can't believe this can be done at first but u showed me otherwise...
I am still trying to figure out part 2 of this puzzle "the issue now is that as you have equated it at row level, returning a 1 which is then SUMed...this SUMs to 2 and not 1 (as both rows, from each team for that person, contain a 1...as both rows qualify)."
This is what i came up with but result is giving me "1", and using a count will give me a "9" where we want an "8" here...
COUNTD(IF [Exceeded 5-10mins (Unsch)]=1 THEN 1 ELSE 0 END)
Appreciate your advice, in the meantime i will keep on trying Thank you again!
-
3. Re: Simple Formula Qns
Simon RuncNov 18, 2016 11:09 AM (in response to Angela.Koh)
1 of 1 people found this helpfulNo problem
So the way, I think to get around this is to use COUNTD, and not 1...
I've assumed here you are trying to count W2K's (if you're not, simply substitute where I used this with the field you want to be a unique count)
In your band calculations change the 1 returned for [W2K]
[Exceeded 5-10mins (Unsch)]
IF [UnschBreak Mins]>35 and [UnschBreak Mins]<40
THEN
[W2K]
END
and then change your measure value aggregations to COUNTD
You are then going to need to change some of your other calculations, to use COUNTD where these fields are referenced as aggregates.
A cheekier way might be to use another LoD, to count the number of non-required rows are with each W2K
IF [Reason Code]="UnscheduledEventsBreak" THEN {FIXED [Reason Code], [Date],[W2K]: COUNT([team])} END
or something like that...and then use this as a divider on the SUM fields, so in the example you posted it would be 2/2=1...and all other dates would be 1/1=1.
The real answer...is to go back and reshape your data so you have it at the grain you want to use it at. I often start with everything in...and then as I develop I go back to the data and re-aggregate to the level I need (eg. if I'm looking at sales by day, I don't need the hourly transactions). I may end up with multiple datasources at different grains, for different calcs/vizes, but use actions to blend/filter across their common dimensions (on a dashboard)...a bit of work, but time you get back 100 fold, in the long run (and makes Tableau that bit more fun!)
-
4. Re: Simple Formula Qns
Angela.Koh Dec 12, 2016 12:32 AM (in response to Angela.Koh)Thanks! You have been a great help, Simon!