Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
1st create a Year field in the script and then you can do this
Dimension
Year
Expression
FirstSortedValue(Value, -Date)
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
Thanks it worked....
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 |
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;
I tried that. But it is not providing expected output.
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
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;
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.