Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Partner
Partner

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
Highlighted

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)

View solution in original post

10 Replies

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.

vgutkovsky
Honored Contributor II

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.

Vlad

Partner
Partner

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.

Partner
Partner

Re: Set Analysis combine FirstSortedValue()

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
Honored Contributor II

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,

Vlad

Partner
Partner

Re: Set Analysis combine FirstSortedValue()

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!

Highlighted

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)

View solution in original post

vgutkovsky
Honored Contributor II

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,

Vlad

Partner
Partner

Re: Set Analysis combine FirstSortedValue()

Thank you Celambarasan,

Save me from a lot of trouble!

Best Regards

Stelios