Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I have been strugling with this for a long time.
I have list box with mtd,ytd and calendar object.
I need to to calculate sum of sales for mtd,ytd for date selected.
This is my sample code
Date:
LOAD * INLINE [
Date, Sales
01/01/2014, 100
02/01/2014, 200
03/01/2014, 300
];
Let varMinDate = Floor(Date#('01/01/2014', 'DD/MM/YYYY'));
Let varMaxDate = Floor(Date#('03/01/2014', 'DD/MM/YYYY'));
Cal:
LOAD
//$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as Date
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
Left Join (Cal)
Load Date as #_Date_NUM_KEY
Resident Cal;
left Join(Date)
Cal_TimeGranule:
Load *, 'YTD' as TimeGranule
Resident Cal where #_Date_NUM_KEY >= YearStart(Date) and #_Date_NUM_KEY <= Date(Date);
Load *, 'MTD' as TimeGranule
Resident Cal where #_Date_NUM_KEY >= MonthStart(Date) and #_Date_NUM_KEY <= Date(Date);
drop Table Cal;
Calendar:
Load Date
Resident Cal_TimeGranule;
Mtd,Ytd logic should be implemented in the script not in set analysis.
Any ideas!! i am not getting desired results.
Thanks
Hi All,
Any How i have done with correct solutions.
Fields might change questiion i have asked and fields in answer.
Let varMinDate = Floor(Date#('01/01/2014', 'DD/MM/YYYY'));
Let varMaxDate = Floor(Date#('06/02/2014', 'DD/MM/YYYY'));
Cal:
LOAD
//$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1,'DD/MM/YYYY') as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
Left Join
load TempDate as Date
Resident Cal;
NoConcatenate
TimeGran:
load *,'MTD' as TimeGranule Resident Cal where
Date >= monthStart(TempDate) and Date<=TempDate
;
Load *,'YTD' as TimeGranule Resident Cal Where
Date >= YearStart(TempDate) and Date<=TempDate;
load *,'Day' as TimeGranule Resident Cal Where
Date = TempDate;
Drop Table Cal;
Calendar:
LOAD Date as TempDate
Resident TimeGran;
Mtd ,ytd should be in one list box.
If i select date and mtd from list box it should display sum of sales for mtd in a text box
Any Ideas !!
Hi All,
Any How i have done with correct solutions.
Fields might change questiion i have asked and fields in answer.
Let varMinDate = Floor(Date#('01/01/2014', 'DD/MM/YYYY'));
Let varMaxDate = Floor(Date#('06/02/2014', 'DD/MM/YYYY'));
Cal:
LOAD
//$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1,'DD/MM/YYYY') as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
Left Join
load TempDate as Date
Resident Cal;
NoConcatenate
TimeGran:
load *,'MTD' as TimeGranule Resident Cal where
Date >= monthStart(TempDate) and Date<=TempDate
;
Load *,'YTD' as TimeGranule Resident Cal Where
Date >= YearStart(TempDate) and Date<=TempDate;
load *,'Day' as TimeGranule Resident Cal Where
Date = TempDate;
Drop Table Cal;
Calendar:
LOAD Date as TempDate
Resident TimeGran;