12 Replies Latest reply on Nov 13, 2018 5:26 AM by sander.binda

# Workaround asteriks with ATTR

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

• ###### 1. Re: Workaround asteriks with ATTR

Hi Sander,

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

BR,

NB

• ###### 2. Re: Workaround asteriks with ATTR

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

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

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

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

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.

Workaround asteriks with ATTR

• ###### 7. Re: Workaround asteriks with ATTR

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.

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

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

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

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

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:

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

I attached this workbook as well.

Hope that helps!

-Chris Chalmers

1 of 1 people found this helpful
• ###### 12. Re: Workaround asteriks with ATTR

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.