2 Replies Latest reply on Jun 7, 2018 11:40 PM by Anton Goncharuk

    Dynamically changing 'Cancellation Rate' in the Calculated Field

    Anton Goncharuk

      Hi folks,

      Can somebody help me to figure out how to implement dynamic variable inside the calculated field, please?

       

      Let me break down the challenge for better understanding:

      • On the worksheet, I broke down Transaction on $100 Bins
      • Broke down by Partners: Booking (Feed = 11) and CJ (Feed = 12)
      • Date filter: All dates, except previous 90 days (Transaction still may be cancelled within 90 days)
      • Coloured Cancelled & Valid Transactions
      • Came up with Cancellation rates for the particular partner and a particular amount of transaction (Bins)
      • Now I want to Calculate Projected Profit using historical Cancellation rate (Except nearest 90 days) by creating new Calculated Field, however, I'm using constant values (type them), but I want them to be dynamically calculated, see screenshot.

       

      Can anybody help me with the calculations?

        • 1. Re: Dynamically changing 'Cancellation Rate' in the Calculated Field
          Sohan Jawar

          Hi,

           

          As per you screen shots I can make out that Cancellation Rate = % SUM(NUMBER OF RECORDS).

           

          Just Edit this field in the Shelf and Use the calculation in a calculated field as Cancellation rate.

           

          Then use this Cancellation Rate in the Calculation of Projected Profit instead of the values.

           

          Also I don't think you would need the condition for [Revenue]<100 or [Revenue] < 200.. etc. As you are using bin in the filter the calculation will be automatically distributed for the cancellation Rate.

           

          Project Profit = [Revenue]*[Cancellation rate]

           

          Just dragging this field should give the required calculation.

           

          Ensure to keep the Compute using Revenue(Bin) for the Cancellation Rate.

           

          Regards

          Sohan.

          • 2. Re: Dynamically changing 'Cancellation Rate' in the Calculated Field
            Anton Goncharuk

            Hi Sohan,

            Thanks for helping out.

            • This is what I use to calculate Cancellation Rate:
              • SUM(IF[Transaction_Status] = 'Cancelled' THEN [Number of Records] END) / SUM([Number of Records])
            • The thing is that I'm NOT going to use Projected Profit within this worksheet, I'll be using on a different worksheet where I don't have a Bins.