topic Re: Latest value for each year in QlikView

Latest value for each year

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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 No	Name	Date	Value
1	A	1/1/2018	1000
2	A	1/2/2018	2000
3	A	1/3/2018	3000
4	A	1/1/2017	1000
5	A	1/2/2017	3000
6	A	1/3/2017	1000

Thanks in Advance

Re: Latest value for each year

sunny_talwar — Fri, 03 Aug 2018 11:13:04 GMT

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

Dimension

Year

Expression

FirstSortedValue(Value, -Date)

Re: Latest value for each year

sibin_jacob — Fri, 03 Aug 2018 11:29:50 GMT

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

Re: Latest value for each year

Anonymous — Fri, 03 Aug 2018 14:55:01 GMT

Thanks it worked....

Re: Latest value for each year

Anonymous — Mon, 06 Aug 2018 04:57:56 GMT

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

Re: Latest value for each year

sibin_jacob — Mon, 06 Aug 2018 05:07:06 GMT

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;

Re: Latest value for each year

Anonymous — Mon, 06 Aug 2018 06:16:57 GMT

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

Re: Latest value for each year

sunny_talwar — Mon, 06 Aug 2018 11:10:22 GMT

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

Selection Year = 2018, org = 12300

Selection Year = 2017 and org = 12300

Selection Year = 2017 and org = 123002

Re: Latest value for each year

sibin_jacob — Mon, 06 Aug 2018 11:41:53 GMT

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;

Re: Latest value for each year

Anonymous — Mon, 06 Aug 2018 16:44:40 GMT

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.

Re: Latest value for each year

sunny_talwar — Mon, 06 Aug 2018 16:49:25 GMT

In text box object... you can try this

=Avg(Aggr(FirstSortedValue(Value, -Date), Name, org, Year_Org))

When org = 12300 is selected

When org = 12301 is selected

Re: Latest value for each year

Anonymous — Tue, 07 Aug 2018 08:09:14 GMT

Thanks a lot .. It worked

Re: Latest value for each year

Anonymous — Tue, 07 Aug 2018 08:09:28 GMT

Thanks for your help...

Re: Latest value for each year

sunny_talwar — Tue, 07 Aug 2018 11:04:02 GMT

I am glad we were able to help you