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: 
agni_gold
Specialist III
Specialist III

FirstSortedValue

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

1 Solution

Accepted Solutions
agni_gold
Specialist III
Specialist III
Author

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

View solution in original post

17 Replies
sunny_talwar

May be with distinct:

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

Chanty4u
MVP
MVP

AGGR( nodistinct     MAX(           AGGR( nodistinct           <formula>

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

     )

, [ERP Amount USD])

Chanty4u
MVP
MVP

you can try with these two ways:

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


or


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

agni_gold
Specialist III
Specialist III
Author

Not working

Kushal_Chawda

try

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

or

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

agni_gold
Specialist III
Specialist III
Author

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.

Clever_Anjos
Employee
Employee

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

agni_gold
Specialist III
Specialist III
Author

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

agni_gold
Specialist III
Specialist III
Author

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