2 Replies Latest reply on Apr 1, 2018 11:04 AM by Rae Ann Boswell

    Using Table Calcs within Fixed calculations/ Implementing "LAG" function in Fixed calculations

    Rae Ann Boswell

      I'm looking to implement the SQL Lag function in tableau, fixed at the customer ID. Because customer's have multiple transaction dates utilizing different segments, I cannot use the LOOKUP or PREVIOUS VALUE function in Tableau within my fixed calculation, since table calculations are not allowed within fixed calcs.  The Lag metric I am looking for in the attached example are:

      - Lag, Date

      - Lag, Segment

      All metrics would need to be fixed at the customer ID. The end goal would be to take the DATEDIFF() between each customer's invoice and the associated Lag invoice.

       

       

      Screen Shot 2018-03-30 at 11.26.33 AM.png

       

      Any tricks or suggestions!?

      Jonathan Drummey Jonathan Drummey

        • 1. Re: Using Table Calcs within Fixed calculations/ Implementing "LAG" function in Fixed calculations
          Jonathan Drummey

          Hi Rae Ann,

           

          Given just a customer ID and invoice date and no other data preparation you'll need to use table calculations for this with formulas like LOOKUP(ATTR([Invoice Date]),-1) and LOOKUP(ATTR([Invoice Segment]),-1), respectively. The reason why is that comparison to prior is a task in Tableau that requires using table calculations.

           

          I'm not sure why you wanted to use a FIXED LOD expression? If it was that you wanted to use the results of the lag calculations as dimension(s) then there are potentially workarounds using additional table calculations, sometimes those aren't feasible and we end up having to do data preparation outside of Tableau. If you can give me more details then I should be able to provide some guidance.

           

          Jonathan

          • 2. Re: Using Table Calcs within Fixed calculations/ Implementing "LAG" function in Fixed calculations
            Rae Ann Boswell

            Hey Jonathon,

             

            Thanks for your response! Here is a more detailed example that may better explain why I need this fixed at the customer ID. The goal is to flag any invoices that could be determined a "follow up" order.... meaning the customer bought something in the store and then had an online order within 2 days after.

             

            For customer 2, if this were not fixed at the customer ID, then record 2 would indicate that this is a "follow up" order when in reality it is not, it is customer 1's order.

            Screen Shot 2018-04-01 at 9.53.42 AM.png

             

            We can have our data team make us this field in SQL (since I know Tableau isn't necessarily a data manipulation tool), but the need to do a table calc (look up function) within a fixed calc comes up for me a lot so wanted to be sure there wasn't a work around for this in Tableau.

             

            Thank you!