2 Replies Latest reply on Aug 8, 2017 10:49 AM by Manuel Velasquez

    Try to get my head around LOD

    Klaas Tea

      Want to see the sales amount for the last order date of the customer but the table aggregates all sales over the 4 years? Why is that. Thanks a lot

       

      last orderdate  { FIXED [Customer ID]:MAX([Order Date])}

      Sales for the last orderdate:   { FIXED [Customer ID],[lastbestellung]:SUM([Sales])} (not working)

        • 1. Re: Try to get my head around LOD
          Jim Dehner

          Klass this is a very good question

           

          the way LOD expressions work is they look at all the dimensions before the colon  :    and create permutations based on those combinations - in you last order expression that is just Customer - so for each customer the expression determines the last order date (Max ) (the first expression

           

          I can't explain what is happening with your second statement - I assume that the second dimension is the last order date you calculated in the first step and when I include it in an LOD expression - like you - I get a sum of all sales -

          But you can use your first expression in either IF statements or as a filter

          in an if statement           if [Order Date]=[fixed last order date] then [Sales] else 0 end

          Will result in this - the first column is all sales - the second is only last date sales

           

           

          As a binary Filter     [Order Date]=[fixed last order date]   (set to true )

          Results in this

           

           

          and with either you can get the lower level detail

           

           

          Let me know if this helped

           

          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.

          • 2. Re: Try to get my head around LOD
            Manuel Velasquez

            Hi Klaas

             

            Another way to get the result you are looking is to nest LOD expression and IF ELSE statement, to tell Tableau for each Customer find the maximum order date, then sum the sales for that day.

            Something like this

             

            1.PNG

             

            To double check the result in Tableau, notice that for each customer you will have one Last Sales value.

             

            2.PNG

             

             

            Removing, Order ID and Order date

             

            3.PNG

             

            Attached .twbx file for reference

             

            Let me know if this helps!

             

            Manuel Velasquez