10 Replies Latest reply: Nov 30, 2017 6:17 AM by Ibrahim Aslan

# Set Analysis combine FirstSortedValue()

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!

• ###### Re: Set Analysis combine FirstSortedValue()

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.

• ###### Re: Set Analysis combine FirstSortedValue()

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.

• ###### Re: Set Analysis combine FirstSortedValue()

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.

• ###### Re: Set Analysis combine FirstSortedValue()

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.

• ###### Re: Set Analysis combine FirstSortedValue()

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,

• ###### Re: Set Analysis combine FirstSortedValue()

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!

• ###### Re: Set Analysis combine FirstSortedValue()

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)

• ###### Re: Set Analysis combine FirstSortedValue()

Thank you Celambarasan,

Save me from a lot of trouble!

Best Regards

Stelios

• ###### Re: Set Analysis combine FirstSortedValue()

Hi Celambarasan and all other guys

a similar issue.

I want to show in the chart the sum of my amount at the last date of a month.

Example

MAT      Amount                Date

123           1                     28.01.2017

123           5                     28.01.2017

124           10                     31.01.2017

125           7                     31.01.2017

126           8                     31.01.2017

Result should be:

Month           Amount

01.2017           25

• ###### Re: Set Analysis combine FirstSortedValue()

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,