1 2 Previous Next 15 Replies Latest reply on Nov 18, 2016 1:15 PM by Sam Bloodgood

    Help with calculating a group of data

    Sam Bloodgood

      Help! I am new with Tableau and LOVE it! 

      I have a record of our monthly shipments by line item (shipment transaction.) One column contains ship qty of item per shipment.

      I want to create a graph that sums total QTY shipped per ITEM (all shipments of that item) and then based on the total shipped per ITEM, which discount group does the ITEM qualify for, and then how many (count) items are per discount group.  I have started one calculated field but I am stuck...

      Thanks for any help...

        • 1. Re: Help with calculating a group of data
          Simon Runc

          hi Sam,

           

          Glad you are enjoying Tableau...it's a pretty awesome bit of kit! (and actually the best thing about it, IMHO, is the Community!!)

           

          So just looking at your Discount Group calculation, and have a question...As you have made it an aggregate calculation (in that the test of which [Ship Qty] get's which discount group, is in a SUM wrapper), it means that the VizLoD (viz Level of Detail) determined how the calcuated field is evaluated (eg, if I remove Item from the columns)

           

           

          I only have one group...the test is SUM([Ship Qty]) and as I no longer have any Item in the vizLoD this is assessed at the total level (25,387 which is >2500 so we only get the single assignment of 50%). I assume you don't want to have to keep Item in the Viz, and would like this calculation to be a "real" dimension, so you can slide the data up by this?

           

          If that's the case can you let me know at what level you want the various tests assessed at? eg. If it's just at item level, then we'd have the same discount group for every occurance of that item, if it's by shipment, then an item could appear in different discount bands depending how many were ordered in that shipment...if that makes sense?

           

          If you let me know this, we can use a FIXED LoD to create the discount group as a proper dimension, so is independant of the VizLoD...if the whole aggregate, VizLoD...etc. makes no sense, this might help Answer - Quora

          this is an explanation of the idea of on-canvas and off-canvas calculation types.

          1 of 1 people found this helpful
          • 2. Re: Help with calculating a group of data
            Sam Bloodgood

            HI Simon,

            I have tried several different ways to figure this out. I sent the one that got me closest...

            To your question;

            If that's the case can you let me know at what level you want the various tests assessed at? eg. If it's just at item level, then we'd have the same discount group for every occurance of that item, - YES

            if it's by shipment, then an item could appear in different discount bands depending how many were ordered in that shipment...if that makes sense? - I got this part (but not a count of pn's in each discount band...

            - I need to SUM the total shipped of each item, then based on the sum, put that sum into the "discount group." and then I would like to know how items are in each group.

             

            Thanks again for working on this.  I will be busy the rest of today, but will check back soon for any help...

            1 of 1 people found this helpful
            • 3. Re: Help with calculating a group of data
              Simon Runc

              Cool...so yes LoDs are one way to go...and especially here as we want to use the groups as a dimension (to slice the data by) without wanting Item in the view every time.

               

              So first I created the following FIXED LoD

              [Ship Qty by Hfitem - LoD]

              {FIXED [Hfitem]: SUM([Ship Qty])}

               

              So this creates (off-canvas) the SUM of Qty aggregated at the Hfitem level. So I can now use this to create the discount groups (I simplified your formula...as IFs exit after a condition is met, we don't need to test for > and <, as long as we have the ELSEIFs in the right order!)

               

              [Discount Groups - SR - LoD]

              IF [Ship Qty by Hfitem - LoD]=1 then "x1.3"

              ELSEIF [Ship Qty by Hfitem - LoD]<=4 THEN "x1.2"

              ELSEIF [Ship Qty by Hfitem - LoD]<=9 THEN "list"

              ELSEIF [Ship Qty by Hfitem - LoD]<=24 THEN "20%"

              ELSEIF [Ship Qty by Hfitem - LoD]<=49 THEN "30%"

              ELSEIF [Ship Qty by Hfitem - LoD]<=99 THEN "40%"

              ELSEIF [Ship Qty by Hfitem - LoD]<=249 THEN "43%"

              ELSEIF [Ship Qty by Hfitem - LoD]<=499 THEN "45%"

              ELSEIF [Ship Qty by Hfitem - LoD]<=999 THEN "48%"

              ELSEIF [Ship Qty by Hfitem - LoD]<=2499 THEN "49%"

              ELSE "50%"

              END

               

              Once I have this, I can use this as a real dimension at will....I've done a few example's

               

              Hope that's what you wanted, and makes sense....let me know if not,

              1 of 1 people found this helpful
              • 4. Re: Help with calculating a group of data
                Sam Bloodgood

                Hello again,

                This got me much further along. Thanks!

                The calculation "ship QTY LOD"

                {FIXED[Hfitem]:SUM([SHIP_QTY])} is giving me the total pieces shipped alltime.

                Is there a way to filter that qty to a specific period?

                • 5. Re: Help with calculating a group of data
                  Simon Runc

                  hi Sam,

                   

                  Yes...so any further cuts of the level at which this is Aggregated can just be added to the left side of the LoD. So to add date, so get a SUM([ship Qty]) for each Item/Date, say, would be

                   

                  {FIXED[Hfitem], [Date]:SUM([SHIP_QTY])}

                   

                  and you can keep adding any other further cuts you want.

                  1 of 1 people found this helpful
                  • 6. Re: Help with calculating a group of data
                    Sam Bloodgood

                    Thanks Simon, I am getting errors trying to enter a date.

                    {FIXED[Hfitem],DATE():SUM([SHIP_QTY])}

                    ERROR - "only column names are permitted...

                    Sorry to bother...

                    1 of 1 people found this helpful
                    • 7. Re: Help with calculating a group of data
                      Simon Runc

                      Yes that DATE() is a function for returning something (say a string) as a date object...you'll need to use the field in your data which relates to date

                       

                      so I think your is [Ivc Date]

                       

                      So would be

                       

                      {FIXED[Hfitem], [Ivc Date]:SUM([SHIP_QTY])}

                      1 of 1 people found this helpful
                      • 8. Re: Help with calculating a group of data
                        Sam Bloodgood

                        Whoa!! Powerful! I love it and Thank you so much for help and understanding.

                        1 of 1 people found this helpful
                        • 9. Re: Help with calculating a group of data
                          Sam Bloodgood

                          Simon, I am still getting strange results. I will continue to work on this and let you know how I get along...

                          • 10. Re: Help with calculating a group of data
                            Simon Runc

                            Yes LoDs are awesome!! (such a clever creation from those clever bods at Tableau!)...they also have 2 cousins (INCLUDE and EXCLUDE LoDs), These are a halfway house between totally fixing the level of aggregation at which a calc is run, and the VizLoD. They are more efficient than FIXED LoDs (in terms of processing time/power), but are a little trickier (conceptually) to set up....certainly worth checking out.

                            • 11. Re: Help with calculating a group of data
                              Sam Bloodgood

                              I will will check them out. I also read your "Quora" reference and will read the other links today...

                              • 12. Re: Help with calculating a group of data
                                Simon Runc

                                It might be that you want these calculated by week, and your data is at day level...so you need to let Tableau know.

                                 

                                So for example...to have this calculated at week level...

                                 

                                {FIXED[Hfitem], DATETRUNC('week',[Ivc Date]):SUM([SHIP_QTY])}

                                 

                                Also you might have other dimensions slicing the data that you either want (as with date), which you'll need to add to the LoD.

                                 

                                The first thing I ask about any data set I have, is what is the "Grain"?...or (another way) "What does a single row of my data represent?". So in the Superstore sales...one row represents an OrderID and Item (in that every OrderID/Item combination is unique). Yes there is Date, Customer, Category...etc. but these don't add to the grain (Date and Customer are simply attributes of OrderID and Category is simply an attribute of Item). Once you answer this, you should be able to decipher at what Grain (or Level of Detail) do I need this calculation run at...and then you should have your answer.

                                 

                                My Flow is always...

                                Data LoD -> Calculation LoD -> Viz LoD

                                 

                                For completeness...this is an oversimplification....when using Aggregate, Table Calcs & Include/Exclude LoDs, I need to know the vizLoD I'm aiming for, in order to determine the best Calculation LoD...but as a general principle it helps me think over my data-challenges.

                                 

                                hope that helps

                                • 13. Re: Help with calculating a group of data
                                  Sam Bloodgood

                                  For some reason I cannot get the {FIXED[Hfitem], DATETRUNC('week',[Ivc Date]):SUM([SHIP_QTY])} "DATETRUC" entered into the calculation. I get an error message

                                  • 14. Re: Help with calculating a group of data
                                    Simon Runc

                                    Ah it's only from Tableau 10, that we can nest calculations in LoDs...

                                     

                                    If you first create a separate calculated field for DATETRUNC('week', [Ivc Date]), and then use that in the LoD instead that'll do the trick

                                    1 2 Previous Next