Set Analysis combine FirstSortedValue()

[steliosg]

Hello,

I have the below expression:

sum({$<TransDate={"=firstsortedvalue({$<CashAmount={'>0'},DATE= {'<$(=OpeningDate)'} >} DATE,-DATE)"}>}Amount).

The firstsortedvalue returns one single date.Alone it works fine.But when i put it on the sum() i get the sum for all values(dates) of field TransDate  instead of

get sum for only single date equal to TransDate.

Any help will be appreciate!

[CELAMBARASAN]

Your expression should look like this

sum({$<TransDate={"=firstsortedvalue({$<CashAmount={'>0'},DATE= {'<$(=OpeningDate)'} >} DATE,-DATE)"},DATE= {'<$(=OpeningDate)', CashAmount={'>0'}}>}Amount)

You need to include DATE field and CashAmount field restrictions in Sum expression also.

[vgutkovsky]

If you're trying to use firstsortedvalue to evaluate a dimensional value in set analysis, then I'm afraid this won't work. All set analysis expressions are evaluated once for the entire chart, rather than per dimension. For example, getting firstsortedvalue(Date) in set analysis will return the first sorted value of all Dates, rather than just the Dates that correspond to the current chart row.

Vlad

[steliosg]

Dear Celambarasan ,

Thank you for your response but the result is the same.The result i get is for all posiible dates for field TransDate.

[steliosg]

Dear Vlad,

I don't hane problem with the firstsortedvalue result.The problem is that the sum() should calculate the Field Amount

for the date 26/9/2012(this is the date that firstsortedvalue() returns) and instead of it calculates Amount for all dates.

[vgutkovsky]

The problem is that the "<" operator is an expression and those can only be evaluated within double-quotes in set analysis, not single quotes. Double quotes = search string or expression; Single quotes = literal value.

Try something like this (haven't tested this):

sum({<TransDate={$(=min({<CashAmount={">0"},DATE={"<$(=OpeningDate)"}>}Date))},DATE={"<$(=OpeningDate)"},CashAmount={">0"}>}Amount)

Regards,

Vlad

[steliosg]

Dear Vlad,

Manage to make it work

sum({$<Date={"=Date(firstsortedvalue( {$<CashAmount={'>0'},Date= {'<$(=OpeningDate)'} >} Date,-Date),'DD/M/YYYY')"}>}Amount)

But the above works only for sign equal and i want the sign  >=

sum({$< Date={">=Date(firstsortedvalue( {$<CashAmount={'>0'},Date= {'<$(=OpeningDate)'} >} Date,-Date),'DD/M/YYYY')"}>}Amount)

Any help will be more than welcomed!

[CELAMBARASAN · Accepted solution]

Your previous one is not working because date field format mismatch with TransDate

For >= check with this

sum({$<Date={">=$(=Date(firstsortedvalue( {$<CashAmount={'>0'},Date= {'<$(=OpeningDate)'} >} Date,-Date),'DD/M/YYYY'))"}>}Amount)

[vgutkovsky]

Again, I suspect the problem is that your second < operation is within single quotes, not double quotes. I think the best bet at making this work is to modify the $(=) logic I had above. Also, assuming OpeningDate is a variable, go to Document Properties >> Number tab, select "Variables" instead of "Fields" in the dropdown, click the OpeningDate variable, and format it as a Date. For some reason, set analysis on dates seems to work much better if the variable that is being compared is in the same format as the date field.

Regards,

Vlad

[steliosg]

Thank you Celambarasan,

Save me from a lot of trouble!

Best Regards

Stelios