8 Replies Latest reply on Apr 11, 2016 12:10 AM by Mahfooj Khan

Hi All,

# Total Sum

123-1Null918
123-1a218
123-1b418
123-1Null118
123-1Null118
123-1Null118
456-1Null216
456-1ab816
456-1bc316
456-1cd116
456-1Null216
777-2Null813
777-2Null213
777-2Null313

Expected Output:

 123-1 b 18 456-1 ab 16 777-2 null 13

Logic:

• To find a category which has max amount for a given Order and show the total amount for that order.
• Show Null in category only when don't see any non-Null category.

Regards,

Nagaraj

• ###### 1. Re: Tableau Calculation

Hello - I have modified your data source a litte bit. Replaced Null with Nul to fool Tableau. Then done the following steps:

1. Created a calculated field "Max Amount Per order" and the expression is {FIXED [Order]: max([Amount])}

2. Created another calculated field "Category " and the expression is if [Amount] = [Max Amount per Order] then [Category] END

3. Created another calculated field "Max Total Value per Order" and the expression is {FIXED [Order]: max([Total Sum])}

4. Exposed the Calculated field "Category " as ATTR

5. Add the Category as the context filter to include and exclude Nulls.

Thanks,

Manideep

• ###### 2. Re: Tableau Calculation

Hi Manideep,

I also want to see the third order 777-2 with Null(Nul)  and show corresponding amount.

If I add to the filter the calculations goes wrong.

I have other filters which will be applied to this data , If I use FIXED LOD will it cause any issue as FIXED happens before filter ?

Regards,

Nagaraj

• ###### 3. Re: Tableau Calculation

Hello - I have checked the "Nul" in the Category filter and get the 777-2 with Nul.

If you want your filter to be applied before LOD then make that as Context Filter. This should solve your issue.

Thanks,

Manideep

• ###### 4. Re: Tableau Calculation

Hi Manideep,

The above results aren't correct .

I have mentioned what is the expected output.

123-1 should show b as its category not null , even if Nul is having the highest but there is non nul value in the order 123-1 which I want to see.

Regards,

Nagaraj

• ###### 5. Re: Tableau Calculation

Hello - You should have uniform logic. If you include null then for the Order "123-1" Nul should return, because Nul Category has the highest Amount (9) for that Order Id.

Thanks,

Manideep

• ###### 6. Re: Tableau Calculation

That's why I explained clearly what I am expecting as an output from the data provided.

• ###### 7. Re: Tableau Calculation

Hello - As you have mentioned that the logic is "

• To find a category which has max amount for a given Order and show the total amount for that order."

So is there a specific logic for that specific Order Id. Please let me know.

Thanks,

Manideep

• ###### 8. Re: Tableau Calculation

Try this

attached 9.2