12 Replies Latest reply on Nov 13, 2018 5:26 AM by Sander Binda

    Workaround asteriks with ATTR

    Sander Binda

      Currently using this formula to show me both order ID's when the distinct row of the order ID exceeds 1 in the category.

       

      Now this only works when there are 2 unique values present. Or let me rephrase this; when the order ID has more than 2 unique values it will only show the first and last unique value.

       

      How do I transform this formula in such a way that it shows all unique values.

       

       

      if COUNTD([Order ID])>1

       

       

      then MIN([Order ID]) + IIF(INDEX()==1, "", ", ") + MAX([Order ID])

       

       

      ELSE

      STR(MIN([Order ID]))

      END

       

       

      Thanks in advance.

        • 1. Re: Workaround asteriks with ATTR
          Naveen B

          Hi Sander,

           

          Could you please attach a sample workbook and O/P how you are expecting

           

          BR,

          NB

          • 2. Re: Workaround asteriks with ATTR
            Sander Binda

            Hi Naveen,

             

            I've added the workbook to my original post. It was a premade workbook used during the training video's on Tableau.com.

            • 3. Re: Workaround asteriks with ATTR
              Cédric Tran

              Hi,

               

              It was not easy to write, but you can try to create this calculated dimension:

               

              if { FIXED [Customer ID], [Ship Mode] : MIN( [Order Date]) } = { FIXED [Customer ID], [Ship Mode] : Max( [Order Date]) }

                  THEN STR([Order ID])

                  ELSE STR({ FIXED  [Customer ID] , [Ship Mode] : MIN(IF [Order Date] = { FIXED [Customer ID], [Ship Mode] : MIN( [Order Date]) } then [Order ID] end ) } )

                      + ' ' + STR({ FIXED  [Customer ID] , [Ship Mode] : MAX(IF [Order Date] = { FIXED [Customer ID], [Ship Mode] : max( [Order Date]) } then [Order ID] end ) })

              end

               

               

              Cedric

              1 of 1 people found this helpful
              • 4. Re: Workaround asteriks with ATTR
                Sander Binda

                Hello Cedric,

                 

                Thank you for replying. I've added the calculated field you proposed but this still returns only the first and last order ID.

                I've added the workbook which now includes the calculated field. I filtered on Customer ID: AA-103751404 which should have 4 different Order ID's in the 'Standard Class' ship mode.

                (Also made a minor change so that it also adds a comma between the different order ID's).

                 

                if { FIXED [Customer ID], [Ship Mode] : MIN( [Order Date]) } = { FIXED [Customer ID], [Ship Mode] : Max( [Order Date]) }

                    THEN STR([Order ID])

                    ELSE STR({ FIXED  [Customer ID] , [Ship Mode] : MIN(IF [Order Date] = { FIXED [Customer ID], [Ship Mode] : MIN( [Order Date]) } then [Order ID] end ) } )

                        + ' ' + STR({ FIXED  [Customer ID] , [Ship Mode] : MAX(IF [Order Date] = { FIXED [Customer ID], [Ship Mode] : max( [Order Date]) } then [Order ID] end ) })

                 

                If I'm not mistaken the calculated field says:

                If the order date is the minimal, first, 'Order Date' belonging to that 'Customer ID' then return the 'Order ID' that belongs to that 'Order Date'

                +

                If the order date is the maximal, last, 'Order Date' belonging to that 'Customer ID' then return the 'Order ID' that belongs to that 'Order Date'

                 

                If this is true, then it still misses the part to return the 'Order ID' of the orders that are in between the first en last order date.

                Is there a way to identify all different values in 'Order date'

                And what if 2 different orders are shipped at the same day with the same ship mode?

                 

                I'm a beginner at Tableau but I would say that the only reason a particular order date is in the data twice is when 2 orders are shipped at the same day.

                If this is true then the calculated field needs to identify every row/value using the 'Order date' and return every 'Order ID' linked to those order dates.

                 

                 

                 

                 

                • 5. Re: Workaround asteriks with ATTR
                  Cédric Tran

                  Hi,

                   

                  I thought that you were looking for the first and last record :-)

                   

                  I have to think about how to integrate order id in between.

                   

                  Maybe someone else could help

                   

                  Cedric

                  • 6. Re: Workaround asteriks with ATTR
                    swaroop.gantela

                    Sander,

                     

                    My apologies at the outset if I have missed the gist, but I think this link may address your issue:

                    Re: Floor Map: how to show more than 1 person per room?

                     

                    In your workbook, I tried a field of:

                    IF FIRST()=0 THEN ATTR([Order ID])

                    ELSE PREVIOUS_VALUE("")+", "+ATTR([Order ID])

                    END

                     

                    The table calculation settings are shown below.

                    This also required a Level of Detail calculation to get the right shipping cost:

                    { FIXED [Customer ID],[Ship Mode]: SUM([Shipping Cost] ) }

                     

                    I think adding in an [OrderDate] will require a bit more finagling but should be doable.

                     

                    Please see workbook v18.1 attached in the Forum Thread:

                    Workaround asteriks with ATTR

                     

                    287202running.png

                    • 7. Re: Workaround asteriks with ATTR
                      Chris Chalmers

                      There is an alternate way to achieve this that puts each Order ID on its own row, which may or may not be what you want. I also took a stab at the Order Date thing, but I'm not sure I understand exactly what you're going for there.

                       

                      Capture.PNG

                       

                      The general idea is:

                      • (optional) Filter to only [Customer Id] + [Ship Mode] + [Order Date] combinations with at least two distinct [Order Id].
                        • { FIXED [Customer Id], [Ship Mode], [Order Date] : COUNTD([Order Id]) }
                        • At least 2.
                      • Add [Customer Id], [Ship Mode], [Order Date], and [Order Id] to the LOD of the viz by putting them on the Rows shelf.
                      • Exclude [Order Id] from the LOD of the shipping cost calculation, so that the sum of shipping cost is aggregated up to the [Customer Id] + [Ship Mode] + [Order Date] level.
                        • ATTR({ EXCLUDE [Order Id] : SUM([Shipping Cost]) })
                      • Because [Order Id] is in the LOD of the viz, there are multiple copies of the same bar for each [Customer Id] + [Ship Mode] + [Order Date] pane. Use a table calc to remove all but the first bar.
                        • IF INDEX() == 1 THEN ATTR({ EXCLUDE [Order ID] : SUM([Shipping Cost]) }) END
                        • Compute Using > Pane (down)
                      • 8. Re: Workaround asteriks with ATTR
                        Sander Binda

                        Hello Swaroop,

                         

                        This is exactly what I was looking for! My goal was to keep the shipping costs aggregrated but with all the order ID present.

                        My apoligies if it confused you but the order date is not relevant for me. I was merely trying to use the order date as a unique identifier to return all relevant order ID's.

                         

                        But since Im still a newbie, could you explain to me why you had to insert a LOD calculation to get the right shipping costs?

                        What does the LOD let you do and the normal sum of shipping costs lack?

                        • 9. Re: Workaround asteriks with ATTR
                          Sander Binda

                          Hello Chris,

                           

                          Altough this was initially not what I was looking for I have to admit that this looks really smooth. I never tought approached the issue from this angle and I think this is even a more clear way to show the order ID's (atleast in this workbook where most shipping modes have about 2 order ID's each per customer).

                           

                          My apoligies for the confusing but the order date is irrelevant, the way its visible in your workbook is fine. I merely tried to use the order date as a unique identifier used to return each order ID linked to an order date.

                          (Guess that's a wrinkle in the approach of someone that does not master Tabluea yet).

                           

                          Anyway thank you all for helping me out, my issue is solved.

                          • 10. Re: Workaround asteriks with ATTR
                            Sander Binda

                            Hello Swaroop, Chris,

                             

                            I've maybe reacted to soon with my previous answers.

                            Firstly Swaroop, I've downloaded the workbook you attached but upon opening I see no data, it's just an empty workbook. I am using 2018.1 version.

                            Secondly Chris, In my workbook I used customer AA-103754104 which has 4 order ID's in shipping mode: standard class. This customer is not visible in your workbook, why is this? It has more than 2 order IDs.

                            • 11. Re: Workaround asteriks with ATTR
                              Chris Chalmers

                              Hey Sander,

                               

                              My mistake - my workbook doesn't show AA-103751404 because I misunderstood what you wanted from dates. It has a filter that excludes any dates with fewer than two orders.

                               

                              On that same workbook, if you remove the filter and replace it with one that only keeps customers/shipping mode combinations with four or more unique orders, you get this:

                               

                              Capture.PNG

                               

                              The customer you were looking at is in the first row.

                               

                              I attached this workbook as well.

                               

                              Hope that helps!

                              -Chris Chalmers

                              • 12. Re: Workaround asteriks with ATTR
                                Sander Binda

                                Hello Chris,

                                 

                                This is exactly what I was looking, thank you. Even without any filter everything still works as intented.

                                I will try to apply this to my private data set which I could not share for the obvious reason and hope the data structure is the same.