topic Re: outstanding claims issue in QlikView

outstanding claims issue

ali_hijazi — Thu, 26 Jan 2012 10:44:11 GMT

Hello

I got a table that contains the following data:

AssessmentID, RecordID, Date, Amount Category

1 13 20/1/2012 1500 A

1 14 20/1/2012 1400 A

1 15 21/1/2012 300 A

1 16 21/1/2012 250 A

2 17 21/1/2012 1500 A

1 18 24/1/2012 700 A

3 19 26/1/2012 650 B

....

Now I have a pivot table with the following dimension:

Re: outstanding claims issue

Miguel_Angel_Baeyens — Thu, 26 Jan 2012 11:48:43 GMT

Hi Ali,

I'd use a very similar example to this or this but using the $(variable) instead of the field in the Aggr() function (untested)

Sum(If(Aggr(NODISTINCT Max(date), Category) = $(variable), Amount))

Hope that helps.

Miguel

outstanding claims issue

ali_hijazi — Thu, 26 Jan 2012 12:08:02 GMT

but the dates that I need to get are not fixed to a value of (variable)

if the user selects 26/1/2012 then I want to calculate the sum of amount for records whose recordid is 17,18 i.e. the dates are 24 and 21 for Category A

and of course record whose recordId is 19 because its date is 26/1/2012

so I can't compare dates to $(variable)

Re: outstanding claims issue

Miguel_Angel_Baeyens — Thu, 26 Jan 2012 12:57:05 GMT

Well, yes.

The above is just an example on how that should work. It seems you want to get the closer date to the one stored in the variable, so you may need additional conditionals, or using less than or equal to the date stored in the variable instead of the equal above.

Hope that helps.

Miguel

outstanding claims issue

ali_hijazi — Thu, 26 Jan 2012 13:35:42 GMT

may you please re-read my questions?

Re: outstanding claims issue

Miguel_Angel_Baeyens — Thu, 26 Jan 2012 13:49:00 GMT

Ali,

Which part I ought to read? If you select from the calendar and the calendar is a table, you are fixing the results since you are already doing a selection. So you need to use a variable instead, populated by a slider/calendar object. And this variable will have as min and max values the min and max values for your date field, that seems to be "Date".

You will need to add the ReportID to the Aggr() function above that is, as I mentioned, an untested example based on working functions as specified in the posts above. Did your read them?

Regards.

Miguel

EDIT: A further read on your questions leaves the doubt on the criteria you are applying to get those amounts. It is not highest RecordID, or the highest Date, so what values do you have to select? Why A for 26/01/2012 returns 2200? That means RecordID is 2 and 1, so it's not the highest. And dates are 21 and 24, so not the highest...

Okay, you are including the AssessmentID. Now that returns as you expected.

outstanding claims issue

ali_hijazi — Fri, 27 Jan 2012 08:02:20 GMT

thank you very much indeed