LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: xathras

Hi,
Does anyone have any tips on how to do tier based calculation

E.g. Lets say my count is 150,000 and the tiers are as follows:

Tier 1.
0-15,000 - Rate = 25%
15,001 - 30,000 - Rate = 22%
30,001 - 45,000 - Rate = 20%
45,001 - 150,000 - Rate = 15%

Typically if you were to implement this in Excel a SUMPRODUCT Function would allow you to do this.

Thanks

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: timonk

xathras,

Do you mean that up to 15,000 records, some rate is 25%, 15,001-30k is 22%, etc? You have a couple options on how to do it.

You could attach an ID field to your input (essentially attach a a count) and do a simple set of "IF count </> #" statements.

You could use an aggregate node, with an aggregate statement of TRUE. THis will count every single record individually. THen you create a local COUNT variable to hold how many records have passed, and use THAT to do your IF statment calculations. This avoids adding a whole new field to your data.

One of those is the way I would probably do it. Depending on how my data was organized. If you are organizing your data differently (or are trying to do somethng other than what I assume above), elaborate a bit and we can try something else.

Regards
Timon Koufopoulos

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: xathras

Hi Timonk,
Yes, lets say I have 150,000 gallons of water.
The first tier is to charge $0.25 per gallon for first 15,000
The next tier is to charge $0.22 per gallon for any usage from 15,001 to 30,000

The list goes on.

Do you have a sample of how this would work?

Thanks

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: michaelslowey

Wayne,

I think this'll do the job.

It takes the usage record, joins it to the rating table so each usage record gets expanded to have an instance with each of the rating table records and then does an agg by customer and totals up the tier amount for each of the expanded usage records.

If you want me to talk it further just gimme a skype.

Cheers
Mick
Attachments:
Tier_Charges.zip