Generating "as of" counts within the UI

[#1]

All,

I'm hoping that someone can assist with this. I first created a support ticket, but they tell me that it is not a support issue. I have a need to calculate totals "as of"  a certain date, but I cannot create this in the LOAD because the user can select any "day" from 1/1/1990 through today. I am able to calculate the amounts "as of" any date without issue. However, I need to calculate the number of claims that were OPEN and CLOSED "as of" whatever date the user chooses. This claim status is on every detail record (RESERVE_DETAIL) and I need to find the LAST value. That's all ... seems simple. I've tried FirstSortedValue function using a -sequence#, MAX function using the sequence#. I can get the totals to work if I use a straight table, but not a pivot table, which is what I need to present users these totals by year. When I try to use the SUM or COUNT functions on fields, it returns null, so I know that can't be correct.

I've attached the .qvw and some word docs that can explain in more detail. I would appreciate any help or advice that you can provide!

[sunny_talwar]

One of the requirements:

Expressions:

Sum(Aggr(FirstSortedValue(DISTINCT OpenClaim, -reserveSeq), fundYear, claim_id))

Sum(Aggr(FirstSortedValue(ClosedClaim, -reserveSeq), fundYear, claim_id))

[#3]

Sunny,

Thank you for your quick reply. I will try this first thing when I get back in the office Monday morning, but it sure looks like that is going to be exactly what I need. Thank you and I will be sure to respond on Monday after I try it out.

Brenda

[#4]

Sunny,

I just wanted to let you know that I have applied the changes you suggested and it works like a charm. You are a genius!

[#5]

Sunny,

I just realized that it's not working exactly as I need it to. It is still using ALL the records in the Aggr(FirstSortedValue ... expression. It is not taking the ending date into consideration. For example, if I want to determine what the open and closed claim count was "as of 12/31/2000", it is showing the open and closed count as of today (over all the records). I need it to only look at the records through the ending date, i.e., date_reserved <= VarDate2. I tried adding this if statement to the front of the expression, but that didn't work. I thought by using the date reserved in the dimension, that would limit the subset of records used in the expressions, but it doesn't.

Please advise.

Thanks,

Brenda

[sunny_talwar]

From the context of your application, what would be a expected output?

[#7]

Sunny,

Please see attached word doc for expected results. Thanks.

[sunny_talwar]

Can you see if these work:

1) Sum(Aggr(FirstSortedValue(DISTINCT {<date_reserved = {"$(='<=' & varDate2)"}>} OpenClaim, -reserveSeq), fundYear, claim_id))

2) Sum(Aggr(FirstSortedValue({<date_reserved = {"$(='<=' & varDate2)"}>} ClosedClaim, -reserveSeq), fundYear, claim_id))

[#9]

Sunny,

That was it! Thank you so much.

[sunny_talwar]

Great, I am glad we were able to figure it out