Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
anisha_nan
New Contributor

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

 

 

 

 

17 Replies
asinha1991
Contributor

Re: firstsorted value in if isnull function

if I understand your Q correctly try this under isnull

check for syntax

FirstSortedValue(Purchase Rate,-Aggr(max(if (not isnull(Purchase Rate),Date)),[Project Name])).

anisha_nan
New Contributor

Re: firstsorted value in if isnull function

Hi ,

Sorry..Expression is not working in straight table.

Regards
MVP
MVP

Re: firstsorted value in if isnull function

To ignore the null values, you could use set analysis, like:

FirstSortedValue(DISTINCT {<[Purchase Rate]={'*'}>} Project Name,
-Aggr(avg([Purchase Rate]),[Project Name]))

Re: firstsorted value in if isnull function


@anisha_nan wrote:

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

Are you looking to display Purchase Rate or Project Name? The above expression will display the project name based on the max Avg Purchase rate among different Project Names. Your description of the problem deviates from your expression. Would you be able to share a sample and provide the expected output from the sample data shared?

anisha_nan
New Contributor

Re: firstsorted value in if isnull function

Hi,
Sample data send needs the Output in straight table.
Please find the attached file.
Do we need to achieve the above requirement at the script level or directly we can add a measure in the straight table in order to achieve Purchase rate.

Regards

Re: firstsorted value in if isnull function

I am not sure if what is provided is the expected output or the sample data.... if it's either of them, then where is the other one 🙂

anisha_nan
New Contributor

Re: firstsorted value in if isnull function

Hi Sunny,

Its sample data and the and in the straight table i am using the same dimension and also mention what needs to consider for Purchase rate calculation.

Regards

Re: firstsorted value in if isnull function

But what are the exact values you are hoping to get? I am not able to translate your statement to numbers 🙂

asinha1991
Contributor

Re: firstsorted value in if isnull function

this works for me(in expression) in straight table provided

a. There is just one entry for latest date for a given project

b. Latest date doesnt have null rate

 

FirstSortedValue(Rate,-Aggr(max(Date),[Project Name]))

the data set you gave is confusing , it has multiple entries for same date...as per what i understand there will be just one entry for latest date