topic Using data with Cut dates in QlikView

Using data with Cut dates

Mon, 11 Jul 2011 12:37:18 GMT

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.

Using data with Cut dates

Mon, 11 Jul 2011 15:19:00 GMT

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

Re: Using data with Cut dates

martin59 — Mon, 11 Jul 2011 15:22:38 GMT

Hi,

You have to do this :

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

Hope this helps you

Martin

Using data with Cut dates

Mon, 11 Jul 2011 16:03:44 GMT

Martin,

Thanks for your reply.

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

Using data with Cut dates

martin59 — Mon, 11 Jul 2011 16:21:17 GMT

How many values do you have for End and Cut ?

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

Using data with Cut dates

Tue, 12 Jul 2011 08:13:12 GMT

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