topic Re: Get greater amount from table using set analysis in QlikView

Get greater amount from table using set analysis

Tue, 12 Jan 2016 19:29:25 GMT

Hi,

I have a table that look like this:

Date Amount

01/01/2013 40,000

01/02/2013 50,000

01/04/2014 55,000

01/06/2015 9,000

I need to take greater amount and the date of this amount.

For example: the result in this case would be 55,000 and 01/04/2014

I have tryied to use FirstSortedValue function but it's doesn't word because it return the first or the las value.

King regards!

Re: Get greater amount from table using set analysis

sunny_talwar — Tue, 12 Jan 2016 19:48:53 GMT

Is amount a field in your database or is this a expression like this -> Sum(Amount)

May be this in a text box:

Amount -> Max(Aggr(Sum(Amount), Date))

Date -> FirstSortedValue(Date, -Aggr(Sum(Amount), Date))

Re: Get greater amount from table using set analysis

Tue, 12 Jan 2016 20:04:11 GMT

No.. this example return the last date.

I just typed an example width the result i need. in the example table the last date is 01/06/2015 but i don't want the last date; i just want the date for that amount. example:

Amount: 55,000

Date: 01/04/2014

your result would by:

Amount: 55,000

Date: 01/06/2015 and that's not correct

Re: Get greater amount from table using set analysis

maxgro — Tue, 12 Jan 2016 20:06:54 GMT

maybe also, in a straight table chart,

sorting by sum(Amount) descending

and in presentation tab max number --> 1

maybe also, in a straight table chart

using dimension limit

Re: Get greater amount from table using set analysis

Tue, 12 Jan 2016 20:17:05 GMT

the problem is tha I'm building the table in memory, so i have an set anlysis using aggr function ordering by date.

this value will be displayed in a textbox object; is for that i want to take the greater amount and his date. the textbox will say:

Greater selling day

01/04/2014

55,000

I'm looking for thats.

Re: Get greater amount from table using set analysis

sunny_talwar — Tue, 12 Jan 2016 21:13:04 GMT

Have you looked at what I proposed for text box object?

Re: Get greater amount from table using set analysis

sunny_talwar — Tue, 12 Jan 2016 21:19:00 GMT

See attached:

Re: Get greater amount from table using set analysis

Wed, 13 Jan 2016 12:42:22 GMT

I've tried your answer but i'm geeting the next error.

Error: Error in expression:

Nested aggregation not allowed

I'm using this expression:

='Greater selling day&Chr(13)&

Concat(FirstSortedValue(Fecha, -Aggr(Sum(Amount), Fecha)))

&Chr(13)& Num(vMaximoCobrado,'#,###.##')

vMaximoCobrado is a variable i have containing the greater amount.

Re: Get greater amount from table using set analysis

sunny_talwar — Wed, 13 Jan 2016 13:26:27 GMT

Why are using Concat() function here? Can you elaborate?

Re: Get greater amount from table using set analysis

Wed, 13 Jan 2016 13:46:16 GMT

the concat function is for concat the Date to text Greater selling day and the amount.

Re: Get greater amount from table using set analysis

sunny_talwar — Wed, 13 Jan 2016 14:52:21 GMT

Try this:

='Geater selling day' & Chr(13) & FirstSortedValue(Date, -Aggr(Sum(Amount), Date)) & Chr(13) & Max(Aggr(Sum(Amount), Date))

Re: Get greater amount from table using set analysis

udaya_kumar — Thu, 14 Jan 2016 07:29:28 GMT

Hi,

Please check this.