14 Replies Latest reply on Feb 13, 2017 2:34 PM by Joshua Milligan

# LOD: Obtain Calc. field output based on multiple column criteria

Hi Tableau Community,

I have the following question and hope you guys can help me out

I have the following data, and my aim is to populate the last 3 columns using the the "User" and "Order Classification":

UserOrder ClassificationSeller1st Order2nd Order3rd Order
Abel1st OrderBlokker
Abel2nd OrderC&A
Abel3rd OrderZara
Greg1st OrderWE
Greg2nd OrderPullman

The output should look as follows:

UserOrder ClassificationSeller1st Order2nd Order3rd Order
Abel1st OrderBlokkerBlokkerC&AZara
Abel2nd OrderC&ABlokkerC&AZara
Abel3rd OrderZaraBlokkerC&AZara
Greg1st OrderWEWEPullmanNULL
Greg2nd OrderPullmanWEPullmanNULL

It seems a lot like a LOD calculation, however I can't really get my head around how transpose the "seller" column over the multiple columns.

Moreover, I would like to avoid doing custom SQL, and do it straight within tableau.

Your help would be super appreciated!

Robbert

1st Order

• ###### 1. Re: LOD: Obtain Calc. field output based on multiple column criteria

Goedenavond Robbert,

Find my approach based on LOD expression below as reference and stored in attached workbook version 9.3

1st order: if [Order Classification]="1st order" then {include [Order Classification]: min([Seller])} END

• ###### 2. Re: LOD: Obtain Calc. field output based on multiple column criteria

I don't understand. I can get that table without any calculations, just arranging the fields:

But there must either be something more complex either in the data or in the desired output.

Robbert, your desired output includes a row for every order and a column for every order classification.  But why?  What's the benefit of that over a simpler table?

Best Regards,

Joshua

• ###### 3. Re: LOD: Obtain Calc. field output based on multiple column criteria

Let's wait for the feedback from Robbert Korthals .

• ###### 4. Re: LOD: Obtain Calc. field output based on multiple column criteria

It is probably because I am new to the community (read: Tableau Noob), but where can I find the attached workbook 9.3?  So far, I am only getting a Count as output, and not the label of the store...

FYI: My aim, when I have the above table format, is to label Abel and Greg either as "Loyal" or "Switcher" based on their first 3 purchasing destinations.

For example: IF ([1st Order] = [2nd Order] = [3rd Order]) THEN "Loyal" ELSE "Switcher")

Again, very much appreciated!

• ###### 5. Re: LOD: Obtain Calc. field output based on multiple column criteria

Robbert,

Your calculation is very close.  Try something like:

IF ([1st Order] = [2nd Order] AND [2nd Order] = [3rd Order]) THEN "Loyal" ELSE "Switcher" END

You can attach the workbook using the link in the reply dialog

Just make sure to attach it as a .twbx (packaged workbook), otherwise it won't include the data and no one else will be able to open it.

Hope that helps!

Joshua

• ###### 6. Re: LOD: Obtain Calc. field output based on multiple column criteria

Haha... a little too quick with the formula, but you are right

But just to check, Norbert Maijoor's example workbook is missing right?

I am still quite curious why I can't make the following formula work

>> 1st order: if [Order Classification]="1st order" then {include [Order Classification]: min([Seller])} END

• ###### 7. Re: LOD: Obtain Calc. field output based on multiple column criteria

Goedenavond Robbert,

Please check the original. The attachment is there

• ###### 8. Re: LOD: Obtain Calc. field output based on multiple column criteria

I could not find the document because of the Internet Explorer browser... Now with Chrome I managed to locate the packaged workbook,

But indeed, as Joshua Milligan mentioned, the formula is not quite what I am looking for as I still would like to have all rows filled as well (in contrast to the image below)

Any ideas?

Robbert

• ###### 9. Re: LOD: Obtain Calc. field output based on multiple column criteria

Robbert,

You can definitely do that.  It's not as complex as writing a calculation for each Order Status.  Here's the approach I took (sorry, I don't have 9.3 installed -- 9.1, 10.0, 10.1, and 10.2 -- but not 9.3!)

As you can see, you can get the rows and columns you want just with the placement of the fields.  Too fill in the NULL values, just use a calculation that looks up the WINDOW_MAX.  Specifically, the code is:

WINDOW_MAX(MAX(Seller))

placed on text and then from the drop down menu on that field select Compute Using > Seller.  That will make sure to find the single non-null value within the Order Classification.  You won't see any values when there hasn't been an order (e.g. the 3rd Order from Greg)

For loyalty, consider a calculation like:

IF {FIXED [User] : COUNTD([Seller])} = 1

THEN "Loyal"

ELSE "Not Loyal"

END

That will determine how many distinct sellers a User has: (1 = Loyal)

It's a FIXED level of detail at the User level, so make sure you understand filter/context filter ramifications.

That can be used easily in the view, something like this:

This approach has a couple of overall benefits versus writing a lot of calculations and avoiding a calculation to compare each order status with others:

• Simplicity
• Flexibility (you're not limited to 3 Order Statuses -- have as many or few as you'd like.

Hope that helps!

Joshua

• ###### 10. Re: LOD: Obtain Calc. field output based on multiple column criteria

Also upon reflection: If the first three order statuses are what's important, then just filter to those and add to context (the FIXED calculation will be done in that context).

• ###### 11. Re: LOD: Obtain Calc. field output based on multiple column criteria

Hmmm... Still not quite what I am looking for... This is because of the following:

1. I am looking for a way to "hard-code" the values over all the rows (eg 1st order) using a calculated field in contrast to what is shown in the table output (ie as you intend to do with your window_max table calculation). The "hard-code" is necessary because later on, I would like to apply the "loyalty-segment" as a split in an area chart on a different worksheet. (Hence, my question is simply an interim step towards my final goal = area chart)

2. It is actually exactly my intention not to have flexibility with the loyalty formula (ie I want to be very explicit which columns to include in the formula -> eg. the 1st order and 2nd order, which is the reason that I am working towards the formula IF ([1st Order] = [2nd Order] = [3rd Order]) THEN "Loyal" ELSE "Switcher")

Again, Joshua Milligan and Norbert Maijoor very much appreciated!

• ###### 12. Re: LOD: Obtain Calc. field output based on multiple column criteria

Robbert,

You mentioned:

... because later on, I would like to apply the "loyalty-segment" as a split in an area chart on a different worksheet. (Hence, my question is simply an interim step towards my final goal = area chart)

Then, I think we're approaching this a bit backwards possibly.  It would be very helpful to understand your end goal much more completely, because it is very likely that there are multiple approaches to get there and what you've asked for might or might not be a necessary intermediate step.

Best Regards,

Joshua

• ###### 13. Re: LOD: Obtain Calc. field output based on multiple column criteria

Let me explain where I am going with this, yet it should be noted from the start that I will step away from my simplified example sketched above, yet fundamentally we are talking about the same challenge:

Basically, this is the table that I am currently looking at. The DeviceIDs are inherently Abel and Greg in our example above

As you can see, I have been able to segment orders as "1st succesful order", "2nd succesful order" and "3rd succesful order", by conducting LOD calculations using a combination of the columns "CreationTime" and "Status - RealOrder Correction" --> basically I have ranked/segmented the orders based on the CreationTime with the condition that the status is "DealerDelivered". So far, so good

Now I intend to create 3 columns in total, named "1st Succesful Order", "2nd Succesful Order" and "3rd Succesful Order" and allocate the "Seller" based on the field value in the column "Order Segmentation". Hence, the top DeviceID should get the seller "Maria" in all the rows for the column "1st Succesful Order", whereas the bottom DeviceID should get the seller "Bob" in all the rows, for the same column (ie "1st Succesful order"). The same logic would then apply to the column "2nd Succesful order" and "3rd Succesful order".

As a result, I will then be able to use the three columns as attributes in a tooltip for a gantt chart that I am creating. It is therefore that the values must be "hard-coded" in the data source.

I hope this helps!

Robbert

• ###### 14. Re: LOD: Obtain Calc. field output based on multiple column criteria

Robbert,

I'm with you up to the "So far, so good".  But I'm afraid I'm still not following why you need three additional columns that are "hard-coded" in the data source to get the right tool-tips on a Gantt chart (there may well be great reason, I'm just not following you to that point yet).  Would you be able to take the sample data you originally provided:

User

Order Classification

Seller

1st Order

2nd Order

3rd Order

Abel1st OrderBlokkerBlokkerC&AZara
Abel2nd OrderC&ABlokkerC&AZara
Abel3rd OrderZaraBlokkerC&AZara
Greg1st OrderWEWEPullmanNULL
Greg2nd OrderPullmanWEPullmanNULL

(with some sample dates) and show me the Gantt chart you would create with the placement of the fields?  That may help me understand why you need the additional three columns.

Best Regards,

Joshua