Using data with Cut dates

[#1]

Hi,

I am currently working on a project that will measure income/cost.   I have attached the an example project.

I am trying to use  'Cut Months At:' to set the 'Cut date' for the Reference.  By altering the cut date, the income will pro-rata.

I am trying to set the table so that any cost that appears after 'Cut date' will not appear within the data - It is this part that I am having a problem with.

I have tried : -

if(Costdate > (addmonths(End, - (12- Cut )))   , 0,   (Cost))

However this does not resolve the issue where there is more than one cost attached to the Reference.

Any help would ve very well received.

Many thanks in advance.

[#2]

Would set analysis be the way to go?

I have just tried

sum({<Costdate={"=< addmonths(End, - (12- Cut ))"}>}Cost)

This doesn't seem to worth either (i'm not too familiar with set analysis so I might ahve the formula wrong)

Thanks

[martin59]

Hi,

You have to do this :

sum({<Costdate={"<=$(=addmonths(End, -(12-Cut)))"}>} Cost)

Hope this helps you

Martin

[#4]

Martin,

Thanks for your reply. 

I have just tried; however it is still producing '£0' values.

[martin59]

How many values do you have for End and Cut ?

if you have more than one value, it doesn't works.

[#6]

Martin,

Essentially this is looking at individual policies for insurance.  The policy/ref can start at any point (usually run 365 days). 

In essence the user should be able to select a renewal month, all policies will be displayed that are due that month.  The cut date can be user defined (based upon months) and it will apply the cut period to all of the individual policies.

The part which I can't get to work is the sum of all claims/costs for an individual policy but excluding the ones that appear after the cut date.

From this I should be able to produce ratios etc.

I hope this explaination makes it a little clearer on what I am trying to achieve.

Many thanks