Hi,
I have a doubt in your second condition,
if(year =2017
Starting value = 100(201706)- that is is least data point record available in 2017 EV= selected field corresponding value
Rest of your condition says whatever the monthyear you select it show previous year Month End as Start date and Selected Date maximum Monthyear is your End Date,
For example :
201908 selected Then SV= 201812 and EV=201912
Please have a look at the below Code which will satisfy your all condition except the above one
SV= Min({1<FS_Date={"$(=Date(YearEnd(Addmonths(Max(FS_Date),-12)),'YYYYMM'))"}>}count)
EV=Min({1<FS_Date={"$(=Date((Max(FS_Date)),'YYYYMM'))"}>}count)
Condition1 Solution :
Minimum Value = Min({<Year={"$(=max(Year))"},
FS_Date = P({1<
Year = {"$(=Max(Year))"},
FS_Date = {"<=$(=min(FS_Date))"}
>} FS_Date) >}count)
Maximum Value=
Max({<Year={"$(=max(Year))"},FS_Date = P({1<
Year = {"$(=max(Year))"},
FS_Date = {"$(=max(FS_Date))"}
>}
FS_Date)>}count)
May it helps you
FS_Date calculated in backend =
Test:
load * inline
[
FS ,count
201706 ,100
201707 ,20
201708 ,500
201709 ,189
201710 ,678
201711, 765
201712, 984
201801 ,234
201802, 985
201803, 678
201804, 436
201811, 458
201812 ,654
201810, 780
201805 ,398
201807 ,1298
201809 ,1287
201806 ,103
201808, 876
201901, 653
201902 ,765
201903 ,765
201904 ,564
201905, 98
201908 ,890
201907, 4532
201906 ,543
201909 ,653
201910, 873
201911, 9853
202001, 986
202002, 876
201912 ,128
202001 ,345
202002, 765
];
NoConcatenate
Test1:
Load *,
Year(FS_Date) as Year;
Load *,
Date(MAkedate(left(FS,4),Right(FS,2) ),'YYYYMM')as FS_Date
Resident Test;
drop Table Test;
exit Script;
