Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
steliosg
Partner - Contributor III
Partner - Contributor III

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!

1 Solution

Accepted Solutions
CELAMBARASAN
Partner - Champion
Partner - Champion

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)

View solution in original post

10 Replies
CELAMBARASAN
Partner - Champion
Partner - Champion

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
Master II
Master II

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
Partner - Contributor III
Partner - Contributor III
Author

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
Partner - Contributor III
Partner - Contributor III
Author

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
Master II
Master II

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
Partner - Contributor III
Partner - Contributor III
Author

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
Partner - Champion
Partner - Champion

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
Master II
Master II

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
Partner - Contributor III
Partner - Contributor III
Author

Thank you Celambarasan,

Save me from a lot of trouble!

Best Regards

Stelios