3 Replies Latest reply on May 18, 2016 8:16 PM by Shinichiro Murakami

    Rental Equipment Incentive Calculator

    Justin Thacker

      I've got a data set of 6,000+ pieces of rental equipment going back 12 mos (~50,000 rows).  I'm trying to build an incentive calculator for rented equipment that would let me:

       

      1. Set bonus rates by equipment type

      2. Set minimum months "active" by equipment type

       

      In the example data set:

       

      1. Equipment type GJC if I select March as my month to validate, then if GJC active for March, February and January but Idle in December then it should produce a "bonus"

      2. Equipment type VJC if I select March as my month to validate, then if VJC active for March and Idle in December then it should produce a "bonus"

       

      I want to be able to select the month I'm checking and then validate back with parameters or something 1, 2 or 3 months of active status.  I've done this in excel using a couple of pivots, but it is very cumbersome.  I was hoping Tableau could help, but I think I need a table calculation and I'm not sure how to do it with the 1, 2 or 3 month test of status.

        • 1. Re: Rental Equipment Incentive Calculator
          Shinichiro Murakami

          Little bit long formula, but basically using LOD with IF statements.

           

          Overview: Level of Detail Expressions

           

          Create "YYYYMM" field to handle month easier

           

          [YYYYMM]

          year([Date])*100+month([Date])

           

          Create month shifted filed.

          [YYYYMM (+1)]

          year(dateadd('month',1,[Date]))*100+month(dateadd('month',1,[Date]))

           

          [YYYYMM (+2)]

          year(dateadd('month',2,[Date]))*100+month(dateadd('month',2,[Date]))

           

          [YYYYMM (+3)]

          year(dateadd('month',3,[Date]))*100+month(dateadd('month',3,[Date]))

           

           

          [GJC Bonus]

          {fixed [Unit No]:sum

          (if [Month to validate]=[YYYYMM]

          and [Status]="Active"

          and [Equipment Type]="GJC"

          then [Number of Records] END)}

          *

          {fixed [Unit No]:sum

          (if [Month to validate]=[YYYYMM (+1)]

          and [Status]="Active"

          and [Equipment Type]="GJC"

          then [Number of Records] END)}

          *

          {fixed [Unit No]:sum

          (if [Month to validate]=[YYYYMM (+2)]

          and [Status]="Active"

          and [Equipment Type]="GJC"

          then [Number of Records] END)}

          *

          {fixed [Unit No]:sum

          (if [Month to validate]=[YYYYMM (+3)]

          and [Status]="Idle"

          and [Equipment Type]="GJC"

          then [Number of Records] END)}

           

           

          [VJC Bonus]

          {fixed [Unit No]:

          sum(if [Month to validate]=[YYYYMM]and [Status]="Active" and [Equipment Type]="VJC"

          then [Number of Records] END)}

          *

          {fixed [Unit No]:

          sum(if [Month to validate]=[YYYYMM (+3)]and [Status]="Idle" and [Equipment Type]="VJC"

          then [Number of Records] END)}

           

           

          [Bonus Flag]

          if ([Equipment Type]="GJC" and [GJC Bonus]>0) or ([Equipment Type]="VJC" and [VJC Bonus]>0 )

          then "Bonus" else " No" END

           

           

           

           

           

           

          Thanks,

          Shin

           

          9.0 attached.

          • 2. Re: Rental Equipment Incentive Calculator
            Justin Thacker

            So does this say VJC gets a bonus after 3 months?

             

            [VJC Bonus]

            {fixed [Unit No]:

            sum(if [Month to validate]=[YYYYMM]and [Status]="Active" and [Equipment Type]="VJC"

            then [Number of Records] END)}

            *

            {fixed [Unit No]:

            sum(if [Month to validate]=[YYYYMM (+3)]and [Status]="Idle" and [Equipment Type]="VJC"

            then [Number of Records] END)}

            • 3. Re: Rental Equipment Incentive Calculator
              Shinichiro Murakami

              You can test with several data

              Supposed to be :

              In a case, "Idle" at three month ago and "Active" in current month, It show the Bonus Flag.

               

              Thanks,

              Shin