outstanding claims issue

[ali_hijazi]

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:

Category

now the user selects from the calendar certain date let's say 23/1/2012

what I want in the pivot table is to show foreach Category  the sum of last recordId whose date is less than 23/1/2012

i.e.

Category,         Amount

A                                1750

whereas if the user selects 26/1/2012 the result should be:

Category,           Amount

A                         2200                              

B                        650

Please advise

[Miguel_Angel_Baeyens]

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

[ali_hijazi]

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)

[Miguel_Angel_Baeyens]

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

[ali_hijazi]

may you please re-read my questions?

[Miguel_Angel_Baeyens · Accepted solution]

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.

[ali_hijazi]

thank you very much indeed