3 Replies Latest reply on Sep 22, 2018 2:11 AM by Steve Martin

    Churn revenue flag not working properly

    Nandan Kumar

      Hi Tableau Giants,

       

      I am trying to create for every email and for every month, the churn revenue flag which is like this:

       

      if {FIXED [Email], Month([Date]):SUM([Revenue])}< {FIXED [Email], DATETRUNC('month',DATEADD('month',1,[Date])):SUM([Revenue])} then 1 else 0 end

       

      But I am getting only 0. I don't know where I am doing mistake. Kindly help.

       

      Regards,

      Nandan

        • 1. Re: Churn revenue flag not working properly
          Steve Martin

          Hi Nandan,

           

          What exactly is this calculation trying to show?

           

          Can you mock something together with Excel showing your Excel calc, this way we can best advise on the calculation and / or approach needed.

           

          Steve

          • 2. Re: Churn revenue flag not working properly
            Nandan Kumar

            Hi Steve,

             

            Thanks for looking into the problem.

            The calculation is trying to check if the previous month's overall revenue of a customer (email is unique identifier of a customer here) is greater than this month's overall revenue or not.

             

            BIG PICTURE:

             

            I am trying to create a month on month nett expansion rate. For that I need churn revenue and expansion revenue.

             

            Formula: (Churn Revenue + Expansion Revenue)/Previous month total revenue

             

            Churn revenue is calculated by adding difference in revenue for every customer if there is a decrease in revenue from previous month.

            Expansion Revenue is calculated by adding difference in revenue for every customer if there is an increase in revenue from previous month.

             

            I was trying to first create a flag that will look for churn/expansion and then for those customers satisfying churn/expansion will be added in their respective bins to get overall churn and expansion revenue.

             

            I have attached the excel and tableau file (with data same as excel). The summary sheet will give you an overview and the 3rd table is what I have been told to generate.

             

            Let me know if you need any other info.

             

            Regards,

            Nandan

            • 3. Re: Churn revenue flag not working properly
              Steve Martin

              Hi Nadan,

               

              Looking at your second message, this looks to be considerably different and a little more involved than your initial request.

               

              Never mind, whilst this is not quite the same, the final output can be used for flagging but, as this requires the use of window calcs, your flag will need to be based off this output instead which would mean, also using a logic statement with the final output.

               

              I have to say, this took some thinking - in all, around 40 mins to complete (my average is around 5 mins for complex calcs) and, the solution was not the easiest to implement, requiring nested window_calcs that needed different scopes to that of the highest-level calc; unfortunately, I hadn't enough time to randomise your numbers to really check the output nb incrementing values of 1,2,3,4,5 for each customer was not great here as it then becomes more difficult to see if the calcs are working as expected but based on these inputs, I am fairly positive this will work as expected; try adjusting the numbers to see if this works.

               

              So onto the necessary calcs: It is best to create the calcs individually because 1. you can check the output of each component [and] 2. Tableau can materialise the values individually before using their values uniquely within the next level; having to materialise during the calculation run-time can return errors ranging from incorrect significance right through to improper set definition leading to unwanted values.

               

              • [Month over Month] - deducts the previous month value from the tested month value:
                • Sum(Revenue) - Lookup(Sum(Revenue),-1)
                • Scoped to Table (across)
              • [Monthly Total] - Sums the total Revenue for the month
                • Total(Sum(Revenue))
                • Scoped to Customer
              • [Monthly Churn] - Sums those values that are less than 0
                • Window_Sum((If [Month Over Month]< 0 Then [Month Over Month] Else Null End))
                • Scope:
                  • Month Over Month = Table (across)
                  • Monthly Churn = Customer
              • [Monthly Expansion] - Sums those values that are greater than 0
                • Window_Sum((If [Month Over Month]> 0 Then [Month Over Month] Else Null End))
                • Scope:
                  • Month Over Month = Table (across)
                  • Monthly Expansion = Customer
              • Net Expansion - Provides the final output for flagging - you can display this value and/or use it in additional calculations such as the flag you wanted to build
                • (Zn([Monthly Churn]) + Zn([Monthly Expansion])) / [Monthly Total] || notice the use of the Zn function (Zero Null) this will return 0 where a null is detected which helps with the final value

                • Scope:
                  • Monthly Churn = Customer
                  • Month Over Month = Table (across)
                  • Monthly Expansion = Customer
                  • Monthly Total = Table (across)
              • Last - as the values will be plotted to every row, this allows us to hide most of the other dimension members whilst still maintaining their presence as required to complete the calculation
                • Last()
                • Scoped to Table (down)
                • Converted to discrete and filtered to 0

               

              i hope this helps.

               

              Steve