Mtd Day Ytd Calculation in script

[Anonymous]

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

[Anonymous]

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

[Anonymous]

Any Ideas !!

[Anonymous · Accepted solution]

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;