topic Re: Set Analysis combine FirstSortedValue() in QlikView

Set Analysis combine FirstSortedValue()

steliosg — Wed, 12 Dec 2012 15:05:02 GMT

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()

CELAMBARASAN — Wed, 12 Dec 2012 15:23:51 GMT

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()

vgutkovsky — Wed, 12 Dec 2012 16:13:58 GMT

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

Re: Set Analysis combine FirstSortedValue()

steliosg — Wed, 12 Dec 2012 16:21:41 GMT

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()

steliosg — Wed, 12 Dec 2012 16:31:58 GMT

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.

Re: Set Analysis combine FirstSortedValue()

vgutkovsky — Wed, 12 Dec 2012 16:58:14 GMT

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

Re: Set Analysis combine FirstSortedValue()

steliosg — Wed, 12 Dec 2012 21:17:11 GMT

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!

Re: Set Analysis combine FirstSortedValue()

CELAMBARASAN — Wed, 12 Dec 2012 21:26:17 GMT

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()

vgutkovsky — Wed, 12 Dec 2012 21:28:14 GMT

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

Re: Set Analysis combine FirstSortedValue()

steliosg — Wed, 12 Dec 2012 21:39:06 GMT

Thank you Celambarasan,

Save me from a lot of trouble!

Best Regards

Stelios

Re: Set Analysis combine FirstSortedValue()

tiklabaq — Thu, 30 Nov 2017 11:17:04 GMT

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