2 Replies Latest reply on May 7, 2018 7:03 AM by Okechukwu Ossai

    LOD Calc Help please.!


      Hi All,


      Need help in getting the below requirement.

      Background: I have customerid, month_year and their transaction data.

                            Want to find the frequency of transactions done by each customerID in last 12 months period.



      Monthly level:

      I am able to achieve this at monthly level by following below steps-

      1. calculated field mthyr = datetrunc('month', month_year)

      2. Fixed calculated field finds number of txns per customer in chosen month = {fixed [customerid], [mthyr] : countd [txnid]}

      This works perfectly @ monthly level.


      Rolling 12 months level:

      However, I am looking to get transaction frequency of each customerID in the last 12 months from the chosen month.

      Example: If i choose Dec 2017 in the mthyr field i want fixed calculation to compute count of multiple transactions done by each customerid from Jan 2017 to Dec 2017.

      Fixed computation should be dynamic based on mthyr selected.


      Request to help me in getting the above.

      Many thanks.

        • 1. Re: LOD Calc Help please.!
          Jim Dehner

          Hi Jay


          its a little difficult working from a description without your twbx workbook - but directionally suggest you looking into using window_sum to total your fixed monthly transactions


          window_sum(sum(fixed monthly),-12,0) will give you the last 12 months - can be used on a dynamic basis


          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: LOD Calc Help please.!
            Okechukwu Ossai

            Hi Jay,


            Another approach will be to use parameters if you want to give users option to choose the dates. I have provided a solution using the Sample Superstore dataset. Replace the fields with the appropriate field names in your dataset. See attached workbook.


            Step 1: Create parameter [Select Month]


            Step 2: Create parameter [Select Year]

            Parameters are not dynamic. But this combination will server its purpose until 2050.


            Step 3: Create calculated field [Selected Date]

            Step 4: Create calculated field [Rolling 12 Months Transactions]



            If you need a filter for other purposes then go the next step.


            Step 5: Create calculated field [Rolling 12 Months Filter]


            Hope this helps.