Shamit,
When it comes to date, make sure to make a thumb rule "Use Master Calender".
What is master calender?
By Taking any date field, which is obviously the field you want to work on Months or Years, and link it with an artificially created table containing your dates and all time fields. This artificially created table is used for all time selections.
Take a look:-
in your case "SKH_RecordDate" date field looks like doing everything.
so do it like
Main:
Load *;
SQL SELECT *
FROM "SKH_StockHistory";
//...........Master Calender Tab
Temp:
load min(SKH_StockHistory]) as Date1,
max(SKH_StockHistory) as Date2
Resident Main;
Let vMin = peek('Date1',0,'Temp');
Let vMax = peek('Date2',0,'Temp');
Calander:
load Date(Date_num,'DD.MM.YYYY') as [SKH_StockHistory],
month(Date(Date_num,'DD.MM.YYYY')) as Month,
Num(month(Date(Date_num,'DD.MM.YYYY'))) as Month_Num,
Year(Date(Date_num,'DD.MM.YYYY')) as Year,
Week(Date(Date_num,'DD.MM.YYYY')) as Week;
load ($(vMin)+IterNo()-1) as Date_num
AutoGenerate(1)
while ($(vMin)+IterNo()-1)<= $(vMax);
Quater:
Load RowNo() as Month_Num,
'Q'&Ceil(RowNo()/3) as Qtr
AutoGenerate(12);
//...............................END...................................................
You can directly copy and paste the script if your sql load table is same with content and field name. Make sure to change the table name at resident command.
Cheers...........................
AK.
