topic Re: FirstSortedValue in QlikView

FirstSortedValue

agni_gold — Mon, 21 Mar 2016 16:26:39 GMT

Hi ,

I am using below expression for calculating sale in maximum date.

=FirstSortedValue({<[ERP Amount USD] -= {0}>} [ERP Amount USD],-[Invoice Date])

But it is returning null values for some rows.

Regards,

Agnivesh

@Sunny T

gwassenaar‌

Re: FirstSortedValue

sunny_talwar — Mon, 21 Mar 2016 16:28:03 GMT

May be with distinct:

=FirstSortedValue(DISTINCT {<[ERP Amount USD] -= {0}>} [ERP Amount USD],-[Invoice Date])

Re: FirstSortedValue

Chanty4u — Mon, 21 Mar 2016 16:31:47 GMT

AGGR( nodistinct MAX( AGGR( nodistinct <formula>

,[ERP Amount USD], [Invoice Date])

)

, [ERP Amount USD])

Re: FirstSortedValue

Chanty4u — Mon, 21 Mar 2016 16:36:56 GMT

you can try with these two ways:

FIRSTSORTEDVALUE (OrderDate, – aggr(sum(Sale)) )

=Max ( aggr ( sum ( Sale ),OrderDate ) )

Re: Calculate the maximum value

Re: FirstSortedValue

agni_gold — Mon, 21 Mar 2016 16:44:13 GMT

Not working

Re: FirstSortedValue

Kushal_Chawda — Mon, 21 Mar 2016 16:44:45 GMT

try

=FirstSortedValue(distinct {<[ERP Amount USD] -= {0}>} [ERP Amount USD],-[Invoice Date])

Sum({<[Invoice Date]={"$(=max([Invoice Date]))"}>} if([ERP Amount USD]>0,[ERP Amount USD]))

Re: FirstSortedValue

agni_gold — Mon, 21 Mar 2016 16:45:21 GMT

when i am using DISTINCT , it is giving syntax error,

but on every max date lot of transactions happen , so i need sum (ERP Amount USD) for that date.

Re: FirstSortedValue

Clever_Anjos — Mon, 21 Mar 2016 16:51:58 GMT

If you have more than one [Invoice Date] which value you want to show?

Re: FirstSortedValue

agni_gold — Mon, 21 Mar 2016 16:56:36 GMT

max(invoice_date) , and sum(erp amount usd).

Re: FirstSortedValue

agni_gold — Mon, 21 Mar 2016 16:57:05 GMT

max(invoice_date) , and sum(erp amount usd) on max(invoice_date)

Re: FirstSortedValue

Clever_Anjos — Mon, 21 Mar 2016 17:00:07 GMT

Try

FirstSortedValue({<[ERP Amount USD] -= {0}>} [ERP Amount USD],-[Invoice Date] + [ERP Amount USD]/1000000 )

Re: FirstSortedValue

agni_gold — Mon, 21 Mar 2016 17:11:39 GMT

No result !!

Re: FirstSortedValue

Chanty4u — Mon, 21 Mar 2016 17:14:38 GMT

cn u share sample qvw?

Re: FirstSortedValue

agni_gold — Mon, 21 Mar 2016 17:18:48 GMT

Hi ,

I am creating a straight table , in which one dimension is present and i have added one expression to find max date using below expression

=Max({<[ERP Amount USD] -= {0}>}[Invoice Date])

Now i want second expression that , on above date sum(ERP Amount USD)

Re: FirstSortedValue

Chanty4u — Mon, 21 Mar 2016 17:32:01 GMT

=Concat(DISTINCT

Aggr( If([ERP Amount USD]= Max([ERP Amount USD]), [Invoice Date], [ERP Amount USD])

,', ')

Re: FirstSortedValue

agni_gold — Mon, 21 Mar 2016 17:42:56 GMT

Thanks but it is also not working

why below expression is not working

=sum({<[Invoice Date] = {"$(=max({<[ERP Amount USD]-={0}>}[Invoice Date]))"}>}[ERP Amount USD])

Re: FirstSortedValue

Chanty4u — Mon, 21 Mar 2016 17:46:06 GMT

try to recheck wit ur data usng metrics in Textbox one by one

Re: FirstSortedValue

agni_gold — Mon, 21 Mar 2016 19:46:27 GMT

Thanks for all your valuable help :

I got answer from below discussion

/thread/81101

Now my problem has been resolved using below expression

=FirstSortedValue( aggr(sum([ERP Amount USD]),[HFM Entity],[Invoice Date]), -aggr(Max({<[ERP Amount USD]-={0}>}[Invoice Date]),[HFM Entity],[Invoice Date]) )