12 Replies Latest reply on Apr 6, 2018 11:31 AM by Simon Runc

    Display respective max month value at each code level

    Ashok Ramineni

      Hello,

       

      I would like to display max month(as highlighted) at each code level and do an aggregation. Can you please help me out here in deriving the LOD.

      Final output should as as shown under Results.

       

       

       

      Thanks,

      Ashok.

        • 1. Re: Display respective max month value at each code level
          Simon Runc

          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

            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

              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

                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

                  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

                    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

                      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, Code-1111, Max(Month) i.e.8/1/2017 has Fee as 27.

                                                                    Code-3333, 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))

                                                                    Code-9999, 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!.

                      • 8. Re: Display respective max month value at each code level
                        Simon Runc

                        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

                          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

                            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)

                            • 11. Re: Display respective max month value at each code level
                              Ashok Ramineni

                              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.

                              Code-1111 : Max month for this code is 8/1/2017 and then look for Fee for this row which is 27.

                              Code-3333 : Max month for this code is 6/1/2017 and then look for Fee for this row which is 15.

                              Code-5555 : 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)

                              Code-9999 : 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, Codes-3333 & 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, Codes-1111 & 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

                                Wow that is very complicated! but seem to have got the right results for the 3 examples.

                                 

                                There's nothing new Tableau-wise 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.