Relating Aggr with If (Uncertain behavior)

[tresesco]

Dear All,

I want a report of Status based analysis. The report should be able to give the report for the "OPEN" or <>3 status of IDs. The Staus need to be calculated based on selection. I have tried it using AGGR function but with some unceratin output. i am attaching my sample application, please have a look at it and help me out.

Regards,

tresesco

[tresesco]

Has anybody a clue?

[#3 · Accepted solution]

Sorry I suggested your 2nd post as an answer when I meant to hit reply. I didn't spent much time looking at your example, but I had a problem that took me forever to figure out and I think it is similar to your problem. Set analysis is only calculated once per table, not per row. That being said, if you want something like max([Invoice Due Date]) for each particular customer, it will not work. If you want something like <today()-31 inside, it will work just fine. In my case, I had to use aggr and if statements. I would try to avoid calculated dimensions at all costs because if you have a lot of data it is INCREDIBLY slow.

Try [DeClaim] as the dimension and this as the expression:

=aggr
(
if([DeDate]=max(total <[DeClaim]> [DeDate]) And [DeStatus]<>3,
sum([DeAmount])
)
,[DeClaim],[DeDate],[DeStatus]
)

If that isn't want you want try this one:

=aggr
(
if([DeDate]=max(total <[DeClaim]> [DeDate]),
if([DeStatus]<>3,
sum(total <[DeClaim]> [DeAmount])
)
)
,[DeClaim],[DeDate],[DeStatus]
)

Pretty sure one of those two are what you want

[tresesco]

Thanks Jones.

Though i had cracked it in other way, your solution gives what i wanted, so its an alternative option for me. That's why i like you people and this community so much, SO MANY CREATIVE AND FERTILE BRAINS ARE HERE ! Once again thanks.

Regards,

tresesco