Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I want to load only most recent records for milestone:-
Milestone Date Value
M1 20201 10
M1 20202 20
M2 20202 30
From Source..
I want to achiever that when i take the Mileston field in front end, it should give only latest record of M1 ie. M1 =20
I think we can achieve it using order by date and peek but not sure. please help.
Thanks
@Aspiring_Developer try below
Data:
Load Milestone,
ProjectCode,
date(date#(Date,'YYYYMM'),'YYYYMM') as Date
FROM Data;
left join(Data)
load date(max(Date),'YYYYMM') as Date
1 as MaxDate_Flag
resident Data;
Now you can use the flag created in measure you use it in chart so your milestone and projectcode will get automatically filtered
=sum({<MaxDate_Flag={1}>}Value)
@Aspiring_Developer looks like you need milestone wise max date and not the max date for all milestone. then change the script as below
Data:
Load Milestone,
ProjectCode,
date(date#(Date,'YYYYMM'),'YYYYMM') as Date
FROM Data;
left join(Data)
load Milestone,
date(max(Date),'YYYYMM') as Date
1 as MaxDate_Flag
resident Data
group by Milestone;
Your expression will remain the same
@Aspiring_Developer in load Script or UI ?
for example in UI you can use FirstSortedValue()
in load Script you can use group by and min or :
Data:
load * inline [
Milestone,Date,Value
M1,20201,10
M1,20202,20
M2,20202,30
];
Final:
noconcatenate
load *,Milestone as k resident Data where not Exists(k,Milestone) order by Date DESC ;
drop table Data;
drop field k;
output :
@Taoufiq_Zarra I have show it in the load script. But i will take this milestone field in the chart as dimension without date . So , how can we get the most recent milestone value without taking date as dimension . please help
@Taoufiq_Zarra Please see the data for your reference
We have dates till 201706 and i want only those M1, M2 values whose date is 202006 . Please help
can you develop more ? what about M3,M4 ... ?
202006 is the max date ? or fixed value ?
@Taoufiq_Zarra The above one worked but i have multiple "Project Codes" against each Milestone . So is there a way we can get the latest milestone for each project with respect to latest Date. Because as of now it is giving me less records like below :-
Is there any way we can get the project codes as well ?
@Taoufiq_Zarra Yes 202006 is maximum date
@Aspiring_Developer try below
Data:
Load Milestone,
ProjectCode,
date(date#(Date,'YYYYMM'),'YYYYMM') as Date
FROM Data;
left join(Data)
load date(max(Date),'YYYYMM') as Date
1 as MaxDate_Flag
resident Data;
Now you can use the flag created in measure you use it in chart so your milestone and projectcode will get automatically filtered
=sum({<MaxDate_Flag={1}>}Value)
Sorry, please see the below as i am still facing the issue please.
Project Code:-C06965 , New_M-:-M1, Date: -201706 till 201712. So it should return the value for 201712 as it is the most recent date for this Milestone and project code:-
Attached the excel for your reference. please help as i am not able to understand how to do this.
@Aspiring_Developer looks like you need milestone wise max date and not the max date for all milestone. then change the script as below
Data:
Load Milestone,
ProjectCode,
date(date#(Date,'YYYYMM'),'YYYYMM') as Date
FROM Data;
left join(Data)
load Milestone,
date(max(Date),'YYYYMM') as Date
1 as MaxDate_Flag
resident Data
group by Milestone;
Your expression will remain the same