
1. Re: Display respective max month value at each code level
Simon Runc Mar 20, 2018 8:51 AM (in response to Ashok Ramineni)hi Ashok,
So one way to do this is with an LoD...
Create the following Calculation
IF [Month] = {FIXED [Code]: MAX([Month])} THEN [Sales] END
Now bring this field into the canvas as a SUM, and bring Category onto Rows, and that should do the trick.
Let me know if that doesn't work.

2. Re: Display respective max month value at each code level
Jim Dehner Mar 20, 2018 8:53 AM (in response to Ashok Ramineni)Good morning
the firs of the max month by category is
datetrunc('month',{ FIXED [Category]: max([Order Date])})
so the sum of sales is equal
if datetrunc('month',([Order Date]))=datetrunc('month',{ FIXED [Category]: max([Order Date])}) then [Sales] end
you could also use an LOD if you find it necessary in a different view
it returns this
Jim
If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution. Thank you.

3. Re: Display respective max month value at each code level
Ashok Ramineni Mar 20, 2018 9:51 AM (in response to Jim Dehner)Simon & Jim ...Appreciate for your prompt reply.
My bad, i thought its gonna work with the logic.. I have figured out where the issue is happening..
let me reiterate my issue..
From the below sample data set, i would like to filter the data based on the below condition.
AMT is not null & Paid<>0
Currently we get max of month at the code level, but I want the max(window max) of month after using the above criteria. Please find the final output under Results.
It would be great if you could provide with LOD & without LOD calc's.
Thanks,
Ashok.

4. Re: Display respective max month value at each code level
Simon Runc Mar 20, 2018 9:56 AM (in response to Ashok Ramineni)hi Ashok,
So using Jim's solution (as he read the question properly, and so included the Max for each month!)
if datetrunc('month',([Order Date]))=datetrunc('month',{ FIXED [Category]: max([Order Date])}) then [Sales] end
We can just adpat the formula, but nesting these extra conditions
if datetrunc('month',([Order Date]))=datetrunc('month',{ FIXED [Category]: max(IIF(NOT(ISNULL([AMT])) AND [Paid]<>0,[Order Date],NULL))}) then [Sales] end
As we are taking the MAX, if anything fails these 2 conditions, then [Order Date[ is set to NULL, and so won't get computed in the MAX
I would query your example as you've said where [Paid] doesn't equal 0 (as an AND with AMT not being NULL), but 2 for the rows you've highlighted, Paid does = 0

5. Re: Display respective max month value at each code level
Ashok Ramineni Mar 25, 2018 8:41 PM (in response to Simon Runc)Thanks Simon.
Yes, the order date will be null if any of these conditions fails.
I have modified with different color code(dark green) for final results.
I have filtered the data in excel based on the above 2 conditions
AMT is not null & Paid<>0
Note: Other measures will go wrong if i filter the data in DB.
Thanks.

6. Re: Display respective max month value at each code level
Simon Runc Mar 20, 2018 10:22 AM (in response to Ashok Ramineni)So assuming that you would want a value included each month (based on the last date, in each month, for each Category/Code combination)
This should do the trick (as I've not used the editor I can't guarantee that I've got all the correct bracketing!)
SUM
(
IF Month] = {FIXED [Category], [Code], DATETRUNC('month',[Month]): MAX(IIF(NOT(ISNULL([AMT)) AND [Paid]<>0,[Month],NULL))}
THEN [Sales] END
)
remove part in bold, if you don't need a new Sales value for each month
If you then bring this field onto the canvas, with Category in the Rows, and it should give you the right result.
Let me know if not, and if possible can you post an example of your Excel (just dummy out some of the columns if the data is sensitive), as it'll be easier to check this is all working against real data.

7. Re: Display respective max month value at each code level
Ashok Ramineni Mar 27, 2018 3:30 PM (in response to Simon Runc)Hi Simon, This helped me a bit to my requirement. However i need to implement another condition to it. Could you please help me out.
Actual Data:
Condition 1:
First I need to filter the data set based on 2 conditions i.e. NOT (Paid is null & Fee is null). Basically I want to exclude all the rows which met this combination together.
Rows 7 & 18 are excluded.
Condition 2:
a) Fee Calculation: Based on condition 1 result set, need to identify maximum Month for each code and then capture the fee value.
Lets say, Code1111, Max(Month) i.e.8/1/2017 has Fee as 27.
Code3333, Max(Month) i.e. 6/1/2017 has Fee as 15.
Code=5555, Max(Month) i.e. 7/1/2017 , Fee should be 50(2 rows: It will have the same Fee, may be we can do AVG or MAX(Fee))
Code9999, Max(Month) i.e. 5/1/2017 has Fee as 45 (3 rows: It will have the same Fee, may be we can do AVG or MAX(Fee))
b) Quantity Calculation: Based on Condition 1 result set + NOT(PSales=0 AND ASales=0) should be used to calculate Qty. We should not include the rows of qty which met this <>0 condition. The values highlighted in Red color should not be part of Qty calc.
Fee value should remains constant even if there is change in the month. Lets say, I select the time period from 2/1/2017 to 3/1/2017. But Fee value should not effect and still holds the Condition 2 values. But Qty will vary based on time period selection.
Month: 2/1/2017 to 8/1/2017
Month: 6/1/2017
Month: 4/1/2017 + 6/1/2017 + 8/1/2017
Note:
 I should not have any conditions applied in filters pane since i display some other measures along with this.
Attached the workbook(10.5) for reference.
I know I am very close to the results, but AVG is not happening for more then 1 Dim values .. If i include the Dim values, then other logic gets failed.. Please advise.
Thanks!.

LOD_10.5.twbx 11.2 KB


8. Re: Display respective max month value at each code level
Simon Runc Mar 28, 2018 1:14 AM (in response to Ashok Ramineni)hi Ashok,
So quite a few questions there!
Just a couple of clarifications....
You said "I should not have any conditions applied in filters pane since i display some other measures along with this"...does that mean on Condition 1? As in that part you seem to suggest you want a filter for that?
btw the formula for that is, pretty much, as you've written it!
NOT(ISNULL([Paid])) AND NOT(ISNULL([Fee]))
What is the final display level you want? As in do you want these values to be calculated by Month, Market, Plan, Code...etc.? This will influence which is the most efficient method.

9. Re: Display respective max month value at each code level
Ashok Ramineni Mar 28, 2018 8:44 PM (in response to Simon Runc)Hi Simon,
"I should not have any conditions applied in filters pane since i display some other measures along with this"
I mean on the quick filters pane(ex: boolean, exclude nulls etc) should not be applied but not the condition 1.
For Ex: I don't want to exclude the records by having this condition NOT (Paid is null & Fee is null) set to TRUE in filters section, instead can be used in calculated fields.
Yes Month, Market, Category should influence the calculated fields.
Please refer to the last 3 screenshots for the expected results based on multiple time periods selection.
Adding some more expected results.
Month: 3/1/2017 & 4/1/2017
Result
Month: 5/1/2017 & 7/1/2017
Market: Europe
Result
Month: 3/1/2017 & 5/1/2017
Market: USA & Canada
Category: Furniture
Result
Hope it helps.
Thank You!

10. Re: Display respective max month value at each code level
Simon Runc Mar 29, 2018 10:53 AM (in response to Ashok Ramineni)hi Ashok,
So I've tried to create the same as your screenshots, but there appears to be something amiss (either my understanding or the rules)
Example 1
Month: 3/1/2017 & 4/1/2017
Result
I thought each Code only took 1 Fee (the last valid date), so not sure how you have the Fee for 9999 at 45 for Canada and 50 for USA. I also have an extra row for 3/1/2017? Unless you want to take the fee by Market, in which case just add this to the LoD, which picks up the last date per code (add Market to the Left and it'll pick up the last date by Code and Market, taking into account the Fees and Paid condition)
Month: 5/1/2017 & 7/1/2017
Market: Europe
Result
I don't have a row for Plan = Medicine and Code = 555. I have 2 CEO rows, which is why the Qty is 15
Month: 3/1/2017 & 5/1/2017
Market: USA & Canada
Category: Furniture
Result
While not being able to "merge" cells we can bring back the MAX of the result for each row. Again here not sure how Code 9999 can take 2 different values.
I've attached what I've done, and here are the general methods
This is how we create a T/F flag
Then we can use this in a formula (so we don't have to filter on it)...and also wrap it in a FIXED LoD, so it gets applied to all rows
Hopefully you can take the general methods and tweak them to get the results you expect (you obviously know the data much better than I can so can extend these methods)

LOD_10.5  SR.twbx 55.1 KB


11. Re: Display respective max month value at each code level
Ashok Ramineni Apr 1, 2018 6:38 PM (in response to Simon Runc)Sorry Simon, if I have not conveyed clearly.
Let me tell you from scratch.
Existing Data :
I should always exclude Paid & Fee is null in the derived logic. i.e. Paid<> null & Fee<>null.(I see your Boolean condition has NOT(ISNULL(PAID)) OR NOT(ISNULL(FEE)), which will pick either of the condition but not both together. It should be NOT(paid=null and fee=null))
If any of the Code is part of results, then below Fee should be displayed irrespective of other Dim values.
Logic for calculating is:
Check for the max(month) at each code level and then pick the relevant Fee for it.
Code1111 : Max month for this code is 8/1/2017 and then look for Fee for this row which is 27.
Code3333 : Max month for this code is 6/1/2017 and then look for Fee for this row which is 15.
Code5555 : Max month for this code is 7/1/2017 and then look for Fee for this row which is 50&50.(In this case Fee is same for both max(month) and code. Hence we need to take an Avg or max which hold correct values of the Fee's)
Code9999 : Max month for this code is 5/1/2017 and then look for Fee for this row which is 45, 45 & 45.(In this case Fee is same for both max(month) and code. Hence we need to take an Avg of the Fee's)
Quantity logic:
We should always calculate quantity based on top of NOT(paid=null and fee=null) condition results i.e. apply Asales<>0 and Psales<>0 to derive Quantity.
SUM(Quantity)
Note: There was a difference between having all 4 conditions together which is not in my case.
Revenue logic:
We need to apply another condition on top of NOT(paid=null and fee=null) results i.e. apply Asales<>0 and Psales<>0 to derive Revenue.
Revenue = SUM(Paid)*(SUM(Psales)/(SUM(Asales)+SUM(Psales)))
IF NOT(paid=null and fee=null) AND Asales<>0 and Psales<>0 then SUM(Measure) END  Wrong Results
IF NOT(paid=null and fee=null) THEN IF Asales<>0 and Psales<>0 then SUM(Measure) END END Expected Results
We need to apply this for all the measures.
i.e.
SUM(IF NOT(paid=null and fee=null) THEN IF Asales<>0 and Psales<>0 then (Psales) END END)
SUM(IF NOT(paid=null and fee=null) THEN IF Asales<>0 and Psales<>0 then (Asales) END END)
I hope you are clear with the above logic so far.
And coming to the examples which has been listed above:
Example 1
Month: 3/1/2017 & 4/1/2017
Ideally, it should retrieve 4 rows, since we used NOT(paid=null and fee=null)) on the whole data set, we get just 3 rows. i.e.
In this case, Codes3333 & 9999 were part of results. Hence Fee would be 15 & 45(as shown above).
Expected Result in the dashboard is just the Category & percent values.
Total Fee logic:
For Code 3333, Fee is 15 & Quantity(Asales<>0 and Psales<>0 ) is 7 for Category=Technology. So 15*7=105
For Code 9999, Fee is 45 & Quantity(Asales<>0 and Psales<>0 ) is 11+14=25 for Category=Furniture. So 45*25=1125.
Percent = Revenue/Total Fee(from above table: example 1)
Revenue = SUM(Paid)*(SUM(PSales)/SUM(Psales)+SUM(Asales))
= 25*(80/(80+77)) = 12.74 for Category = Technology
= 65*(45/(45+84)) = 22.67 for Category = Furniture
Example 2:
Month: 5/1/2017 & 7/1/2017
Market: Europe
After applying the Market=Europe, It should retrieve 3 rows, since we used NOT(paid=null and fee=null)) on the whole data set.
In this case, Codes1111 & 5555 were part of results. Hence Fee would be 27 & 50(as shown above).
Expected Result in the dashboard is just the Category & percent values.
Total Fee logic:
For Code 1111, Fee is 27 & Quantity(Asales<>0 and Psales<>0) is 1 for Category=Technology. So 27*1=27
For Code 5555, Fee is 50 & Quantity(Asales<>0 and Psales<>0) is 10+5=15 for Category=Furniture. So 50*15=750.
Percent = Revenue/Total Fee(from above table: example 2)
Revenue = SUM(Paid)*(SUM(PSales)/SUM(Psales)+SUM(Asales))
= 80*(110/(110+42)) = 57.89 for Category = Technology
= 10*(20/(20+11)) = 6.45 for Category = Furniture
Example 3:
Month: 3/1/2017 & 5/1/2017
Market: USA & Canada
Category: Furniture
Ideally, it should retrieve 6 rows, since we used NOT(paid=null and fee=null)) on the whole data set, we get just 5 rows. i.e.
In this case, Codes 9999 only were part of results. Hence Fee would be 45 (as shown above).
Expected Result in the dashboard is just the Category & percent values.
Total Fee logic:
For Code 9999, Fee is 45 & Quantity(Asales<>0 and Psales<>0) is 14+10+15=39 for Category=Furniture. So 45*39=1755.(Quantity=12 will not be part of calculation because of Asales<>0 and Psales<>0 condition)
Percent = Revenue/Total Fee(from above table: example 3)
Revenue = SUM(Paid)*(SUM(PSales)/SUM(Psales)+SUM(Asales))
= 113*(10/(10+55)) = 17.38 for Category = Furniture
Please let me know if you need any other details.
I know it's very complicated and appreciate your effort in addressing it.
Thanks.

12. Re: Display respective max month value at each code level
Simon Runc Apr 6, 2018 11:31 AM (in response to Ashok Ramineni)Wow that is very complicated! but seem to have got the right results for the 3 examples.
There's nothing new Tableauwise here (using LoDs to apply the Fee to all other Rows, and T/F logic statement to either filter the table, or within aggregated formulas to restrict how they calculate). I've added a 'SR' append to each of the formulas, so you can work back through what each is doing.
Tableau isn't ideal for this kind of thing (Excel excel's, if you excuse the pun!, where you want to work up logic cell by cell, whereas Tableau thinks like a database so applies aggregate functions over the entire 'set' of value and the VizLoD is used to alter the aggregation level...but with LoDs and Logic Statements nested in aggregate function we can just about get it to bend to our will!)
Hope that helps, and makes sense.