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: 
anisha_nan
Contributor II
Contributor II

firstsorted value in if isnull function

Hi,

In the straight table i need to display project name,date,id and Purchase rate

1. I have requirement to get latest Purchase Rate on the basis of date for the particular Project.

2. If the Purchase Rate is Null for the specific line item then automatically it should pick the latest Purchase rate on the basis of date wherever the Rate is available .

 i have try with above expression :

FirstSortedValue(DISTINCT Project Name,-Aggr(avg([Purchase Rate]),[Project Name])).

Can please correct me on the expression for the above requirement.

 

Regards

 

 

 

 

Labels (2)
17 Replies
anisha_nan
Contributor II
Contributor II
Author

Hi Sunny,

 

Please find the attached Data Set in which i have mentioned what needs to come in the Purchase rate .

I hope its clear now: smileyhappy:

Regards

 

sunny_talwar

It makes sense most of the time, expect one thing... why do we have Supplier A, Invoice 1001, Pricing category Price1 repeat 2 times for each set of Project Name

image.png

How do I differentiate between these two rows in the each block... why don't we have this as the solution

image.png

600 because the combination for Supplier A, 1001, Price1 has 600 as it's most recent value.

 

anisha_nan
Contributor II
Contributor II
Author

Hi Sunny,

I Forgot to mentioned that we have two pricing category and for this Specific Requirement to get Purchase Rate we need to check only for Price 1 with all combination.

Regards
sunny_talwar

Price 1 is what I am looking at 🙂

anisha_nan
Contributor II
Contributor II
Author

Hi Sunny,

 

Please find the attached data set and the actual requirement is also mention in it.

Regards 

sunny_talwar

Checking

sunny_talwar

Is this your data?

Project Name	Date	Supplier	Invoice No	Pricing category 	Rate
abc	1/2/2018	Supplier A	1001	Purachse Price	100
abc	1/2/2018	Supplier B	1001	Purachse Price	200
abc	1/2/2018	Supplier A	1001	Selling Price	150
abc	1/2/2018	Supplier A	1001	Selling Price	300
xyz	1/3/2019	Supplier A	1001	Purachse Price	
xyz	1/3/2019	Supplier B	1001	Purachse Price	
xyz	1/3/2019	Supplier A	1001	Selling Price	400
xyz	1/3/2019	Supplier A	1001	Selling Price	
def	1/5/2018	Supplier A	1001	Purcahse Price	
def	1/5/2018	Supplier B	1001	Purcahse Price	500
def	1/5/2018	Supplier A	1001	Selling Price	600
def	1/5/2018	Supplier A	1001	Selling Price	700

or is this your data

Project Name	Date	Supplier	Invoice No	Pricing category 	Rate
abc	1/2/2018	Supplier A	1001	Purachse Price	100
abc	1/2/2018	Supplier B	1001	Purachse Price	200
abc	1/2/2018	Supplier A	1001	Selling Price	150
abc	1/2/2018	Supplier B	1001	Selling Price	300
xyz	1/3/2019	Supplier A	1001	Purachse Price	
xyz	1/3/2019	Supplier B	1001	Purachse Price	
xyz	1/3/2019	Supplier A	1001	Selling Price	400
xyz	1/3/2019	Supplier B	1001	Selling Price	
def	1/5/2018	Supplier A	1001	Purcahse Price	
def	1/5/2018	Supplier B	1001	Purcahse Price	500
def	1/5/2018	Supplier A	1001	Selling Price	600
def	1/5/2018	Supplier B	1001	Selling Price	700
anisha_nan
Contributor II
Contributor II
Author

Hi,

Please ignore all the data set which was send earlier and refer only the data set which was send yesterday.

Regards