7 Replies Latest reply on May 13, 2018 2:39 AM by JayC

    LOD Calculation Help Please!

    JayC

      All Tableau experts,

      I am not familiar with LOD's and need to use this for getting the task done.

       

      Request to assist with the below need:

      Need to get the frequency of orders made by customer from the chosen month_year and allocate customers into 1time (1x) and  >1time (2+) bucket.

      My data contains - CustID, Orderid, Month_year, Channel (retail, desktop, mobile).

       

      I am able to get some part of it by below calculation:

      if

      {FIXED [CustID],[Channel],[Month_year] : COUNT([Orderid])}

      >1 then '2+' ELSE '1x' END

      The above gives number of orders a customer makes through a specific channel in a specific month and assign '2+' if the number is greater than 1.

      But, what if a customer has made multiple orders using two different channels. Customers end up geting counted twice leading to wrong count..

       

      Is there a way to modify the above calculation such that the customer gets counted only once.?

       

      If a customer makes orders using multiple channels then I would like to get total number of orders done per channel and consider the max of it.

      Example say customer 'Christine' has made 2 orders using retail and 1 order via mobile then the total number of orders made will be 3 AND customer should be counted only once accounted from channel retail NOT from mobile and allocate into 2+ bucket.

      Note: In my real case scenario, i have many fields similar to channel above where customer must be subjected to right calculation to get desired results.

      I also wanted month_year, channel filters offered to end users such that users can pick their own choice and calculation doing its task.

      please help.

       

      Many Many thanks in advance.

        • 1. Re: LOD Calculation Help Please!
          Deepak Rai

          {FIXED CustID,MONTH_Year:MAX({FIXED [CustID],[Channel],[Month_year] : COUNT([Orderid])})}>1 then '2+' ELSE '1x' END

          1 of 1 people found this helpful
          • 2. Re: LOD Calculation Help Please!
            JayC

            Thanks a lot for your response Deepak.

             

                 If possible request to provide few clarifications -

            1. What if i want to add more dimensions to fixed calculation, say "Country" or "Category" or any other as such......

                 These dimensions will have to be added to the inner fixed/parent fixed or both?

                 Can you please modify and post the syntax in this scenario.

                 Will the performance deteriorate since my final table is an extract sitting on server with this calculation created as 'one-off'      adhoc calculation.?   

             

            2. Your calculation works in some cases but shows discrepancy while matching 1+ ordered customers number with total number of orders.
                 I meant #of customers who have ordered only once is not aligning with the total #of orders which ideally should be a dot match.

             

            3. How can i revise the above syntax for getting data for last 12 months from the chosen month.
                Can we define fixed to compute within a specific period?
                Say if user selects Mar 2018 it should take data from Apr 2017 till Mar 2018 and give me customer frequency (just like above      case but not at monthly level but instead at rolling 12 month level).

            I am aware of dateadd function and have tried adding  >= -12 months from the selected month and <= chosen month but dinn work as expected.

             

            Once again appreciate all the time devoted in helping me.


              

            • 3. Re: LOD Calculation Help Please!
              Deepak Rai

              Do you have a fake workbook similar to your actuals to answer your questions? If yes, pl post and write your  one liner  questions as 1,2,3

              • 4. Re: LOD Calculation Help Please!
                Deepak Rai

                1.

                 

                You are first looking for Maximum from all channels per country or category and the Fix that Maximum for that person per month

                 

                so it will go inside

                 

                {FIXED CustID,MONTH_Year:MAX({FIXED [CustID],[Channel],[Month_year], Country, Category : COUNT([Orderid])})}>1 then '2+' ELSE '1x' END

                 

                It depends if u want to add company, category or both.

                 

                2. Please attach fake data and I will show you

                3. Yes, it can be done then Fixed can be asked to operate for  a time interval only

                Thanks

                Deepak

                1 of 1 people found this helpful
                • 5. Re: LOD Calculation Help Please!
                  JayC

                  Hi Rohit,

                   

                  As requested sample data is now part of the attached Tableau file.

                  Please go through it.

                  • 6. Re: LOD Calculation Help Please!
                    Deepak Rai

                    pl see attached. you need to add any filter to CONTEXT for LOD to operate.

                    Thanks

                    Deepak'

                    if it Helps, pl mark it Helpful and CORRECT to close Thread

                     

                    • 7. Re: LOD Calculation Help Please!
                      JayC

                      Hi Deepak,

                       

                      Thanks again for taking time to reply with solution.

                      I have cross checked the output with the backend excel and its not aligning.

                      I checked for Ind to Ind for last 3 months (Mar-Apr-May 2018).

                       

                      Also, you did say abt fixed calculation computing to work within a defined period. I did not see that calculation.

                      Am i missing or doing something wrong here??. Please advise.