Help with formula and indexing arrays with variable names

[huytonscouser]

This is the data :-

Policy Data Device GB used Period days Bronze-VTIER15-*25.00*Bronze-VTIER3-*75.00* abc 34.14 31
Gold-VTIER0-*5.00*Gold-VTIER15-*30.00*Gold-VTIER3-*65.00* xyz 620.38 31
Platinum-VTIER0-*8.00*Platinum-VTIER15-*42.00*Platinum-VTIER3-*50.00* jah 182.45 31
Silver-VTIER0-*1.00*Silver-VTIER15-*25.00*Silver-VTIER3-*74.00* fad 325.62 31


//This is the rates per year

// 2012 2013 2014 2015 2016 vtier0 rates
shared numberVar Array vtier0 := [4298.00, 3826.35, 3387.55, 3003.51, 2663.01];
// 2012 2013 2014 2015 2016 vtier1 rates
shared numbervar Array vtier1 := [1,323.00, 1,214.94,1,111.90,1,021.41,938.29];
// 2012 2013 2014 2015 2016 vtier15 rates
shared numbervar Array vtier15 := [1,323.00,1,214.94,1,111.90,1,021.41,938.29];
// 2012 2013 2014 2015 2016 vtier2 rates
shared numbervar Array vtier2 := [1,048.20,962.58,880.95,809.26,743.40];
// 2012 2013 2014 2015 2016 vtier3 rates
shared numbervar Array vtier3 := [626.32, 580.99, 527.65, 479.34, 435.45];


I want to create a formula to calculate {Daily Rate}

Daily Rate :=({GB used}/1024)*0.05) *VTIER0 array[year])/{Period days} + {GB used}/1024)*0.3) *VTIER15 array[year])/{Period days} + {GB used}/1024)*0.65) *VTIER3 array[year])/{Period days]


i.e. example for device xyz (hand coded)

Daily Rate :=(620.38/1024)*0.05) *4298)/{Period days} + 620.38/1024)*0.3) *1323)/{Period days} + 620.38/1024)*0.65) *626.32)/{Period days]

From {Policy Data} and year 2012 and Period days = 31

i.e FROM THIS DATA
Gold-VTIER0-*5.00*Gold-VTIER15-*30.00*Gold-VTIER3-*65.00*

Get the VTIER0 rate for 2012 (4298)/1024 (terabyte) times it by 5% / {Period days} (31)
PLUS
Get the VTIER15 rate for 2012 (1323)/1024 (terabyte) times it 30% / {Period days} (31)
PLUS
Get the VTIER3 rate for 2012 (626.32)/1024 (terabyte) times it 65% / {Period days} (31)



I'm struggling with making this over cumbersome. Just need some help to structure array for rates, or create a dynamic array which is the % times the rate.

I know this is probably complicated on the face, but there are many "formula" experts who can
make sense of this, and help..... i'm hoping anyway . Point me down the path !!! or kick me down.

 

[Charliy]

I'm not real clear on what your data looks like so I'm going to make a few assumptions:
1. There is a date in each record - if not, then somewhere you have a date that this report is for
2. That each Tier and Usage in the record are in a separate field or a child record - if it's all one string then you'll have to split it into formulas
3. Gold, Silver, and Bronze do no affect the calculations

First thing you'll need is a number to tell you which rates to use. Create a formula RYEAR like this:
onfirstrecord;
year({your date field})-2011

This will give you 1 for 2012, 2 for 2013, etc. You can use it to access the correct year's rate like this:
vtier0[{@RYEAR}]

Now, if the various tiers and usages are separate fields (or formulas if you had to split) then you can do something like this:
if {table.tier} = 'VTIER0' THEN vtier0[{@RYEAR}]
ELSE
IF {table.tier} = 'VTIER1' THEN vtier1[{@RYEAR}]

and so on