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: 
Anonymous
Not applicable

Latest value for each year

Hi Everyone,


I want to include two years of data into the report and i need to find latest value based on date column for each particular year.

For 2018 the latest value is 3000. For 2017 latest value is 1000.

 

Rec NoNameDateValue
1A1/1/20181000
2A1/2/20182000
3A1/3/20183000
4A1/1/20171000
5A1/2/20173000
6A1/3/2017

1000

Thanks in Advance

1 Solution

Accepted Solutions
sibin_jacob
Creator III
Creator III

Please create flag for latest date in a Year using the below script.

//Table 1 is existing table

Table2:

Load RecNo, Name,Date,Value,

if(year(Date)=previous(Year(Date),0,1) as Latest_Date_Year_flag

resident Table1 order by Date desc;

Use Latest_Date_Year_flag column for filtering your data,


only latest date in each year will get value as 1


Thanks,

Sibin


View solution in original post

13 Replies
sunny_talwar

1st create a Year field in the script and then you can do this

Dimension

Year

Expression

FirstSortedValue(Value, -Date)

sibin_jacob
Creator III
Creator III

Please create flag for latest date in a Year using the below script.

//Table 1 is existing table

Table2:

Load RecNo, Name,Date,Value,

if(year(Date)=previous(Year(Date),0,1) as Latest_Date_Year_flag

resident Table1 order by Date desc;

Use Latest_Date_Year_flag column for filtering your data,


only latest date in each year will get value as 1


Thanks,

Sibin


Anonymous
Not applicable
Author

Thanks it worked....

Anonymous
Not applicable
Author

Hi Sibin,


Thanks for your reply. Suppose if i have data like below table, I need to find the latest value based on date column for each particular year. It should work based on org filter.

For 2018 the latest value for org 12300 is 3000. For 2017 latest value for org 12300 is 3000.

For 2017 latest value for org 12302 is 3000.

   

Rec NoNameDateValueorg
1A1/1/2018100012300
2A1/2/2018200012301
3A1/3/2018300012300
4A1/1/2017100012301
5A1/2/2017300012300
6A1/3/2017300012300
7A1/1/2017100012302
8A1/2/2017300012302
sibin_jacob
Creator III
Creator III

Try this one

Table2:

Load RecNo, Name,Date,Value,

if(org=prev(Org),0,

if(year(Date)=previous(Year(Date),0,1)) as Latest_Date_Year_flag

resident Table1 order by Date,org desc;

Anonymous
Not applicable
Author

I tried that. But it is not providing expected output.

sunny_talwar

Did you ever get a chance to try this

FirstSortedValue(Value, -Date)

This might need modification based on your data and needs, but with a little modification... the expression should work for you... for example... If you want to create a chart with Name, Year, and org as dimension... and the above expression... you will see this

Capture.PNG

Selection Year = 2018, org = 12300

Capture.PNG

Selection Year  = 2017 and org = 12300

Capture.PNG

Selection Year = 2017 and org = 123002

Capture.PNG

sibin_jacob
Creator III
Creator III

I have made few changes in the script, please check now,

Test:

LOad *Inline [

Rec No,Name,Date,Value,org

1,A,1/1/2018,1000,12300

2,A,1/2/2018,2000,12301

3,A,1/3/2018,3000,12300

4,A,1/1/2017,1000,12301

5,A,1/2/2017,3000,12300

6,A,1/3/2017,3000,12300

7,A,1/1/2017,1000,12302

8,A,1/2/2017,3000,12302

];

//exit SCRIPT;

Test1:

NoConcatenate

Load [Rec No],Name,

Date(Date#(Date,'DD/MM/YYYY'),'DD/MM/YYYY') as Date,

Value,org,

year(Date(Date#(Date,'DD/MM/YYYY'),'DD/MM/YYYY'))& org as Year_Org

Resident Test

order by Date desc;

DROP Table Test;

//Exit SCRIPT;

Table2:

Load [Rec No], Name,Date,Value,org,

if(Year_Org=previous(Year_Org),0,1) as Latest_Date_Year_flag,

Year_Org

resident Test1

//where org=12302

order by Year_Org desc;

DROP Table Test1;

Anonymous
Not applicable
Author

Hi Sunny,

Thanks for the response. It is working fine in the charts. I want to display average of first sorted value in the text box.

For 12300 (Latest value from both the years), average of first sorted value is  (3000+3000)/2 =3000 .

For 12301 Average of first sorted value = (1000+2000)/2.

Untitled.png