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

# Rental Equipment Incentive Calculator

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

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)]

[YYYYMM (+2)]

[YYYYMM (+3)]

[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

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

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