4 Replies Latest reply on Dec 12, 2016 12:32 AM by Angela.Koh

    Simple Formula Qns


      Hi all Experts!


      I have a qns on this formula... I am grouping the duration into time buckets but in this case - data for 20 Oct is missing. It shows 37 mins but it is not accounted into the time bucket of "Exceed 5to10mins". Reason is because on 20 Oct this staff has switched department and the data of 37mins is split into 13 mins and 24 mins... How do i actually have the split duration summed up and show up in my time bucket sheet?  Thank you in advance for your help and time!


        • 1. Re: Simple Formula Qns
          Simon Runc

          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

            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 Runc

              No 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





              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!)

              1 of 1 people found this helpful
              • 4. Re: Simple Formula Qns

                Thanks! You have been a great help, Simon!