Day ,Mtd ,Day in load script

[Anonymous]

Hi

I calculated mtd ytd day calculation in script .I tried it in set analysis but my application is getting bigger so we thought to implement mtd,ytd logic in script.

I got values for day ,but  for mtd ytd i am not able to pull the data.

My aplication consist of calendar object ,list box and sum(Sales)

I'll select date and mtd or ytd or day from list box and corrresponding sum of sales will be displayed.

My code is below .

Cal:

LOAD Date ($(StartCalendar) + RecNo()-1) as CalDate

AutoGenerate ((today()-1) - $(StartCalendar)+1 );

//Left Join with no keys

Left Join (Cal)

Load CalDate as #_Date_NUM_KEY

Resident Cal;

//Bringing Accumulated YTD granule

NoConcatenate

Cal_TimeGranule:

Load *, 'YTD' as TimeGranule

Resident Cal where #_Date_NUM_KEY  >=  YearStart(CalDate) and #_Date_NUM_KEY <= CalDate;

//Bringing Accumulated MTD granule

Concatenate

Load *, 'MTD' as TimeGranule

Resident Cal where #_Date_NUM_KEY  >=  monthstart(CalDate) and #_Date_NUM_KEY <= CalDate;

//Bringing Accumulated Day granule

Concatenate

Load * , 'Day' as TimeGranule

Resident Cal

where #_Date_NUM_KEY = CalDate;

drop Table Cal;

CAL:

Load

    CalDate,

//    Date(SALES_DATE, 'DD/MM/YYYY') as SALES_DATE_1,

    Month(CalDate) as SALES_MONTH,

    Year(CalDate) as SALES_YEAR,

    MonthName(CalDate) as SALES_MONTHNAME,

    'Q' & Ceil(Month(CalDate)/3) AS SALES_QUARTER,

    DayNumberOfYear(CalDate) as DayNumberOfYear,

    Day(CalDate) as DayNumberOfMonth,

    DayNumberOfQuarter(CalDate) as DayNumberOfQuarter,

    num(Month(CalDate)) as MonthNum

    Resident Cal_TimeGranule;

Thanks

[Anonymous]

When i Click on Mtd or Ytd list box i should get sum(sales)  for Ytd and mtd.in a text box.

let's assume if i select mtd in lsit box i want sum of sales for mtd in text box

[kavita25]

please provide you qvd...so that I can test...

and also wat about the calendar object, if I select  1/1/2014 or any date...what should day, mtd and Ytd list box should show??

Is it

for day- the sum of sales for that particular date

for mtd-whole of jan month

for ytd-whole of 2014

[Anonymous]

Hi

Calendar object is Tempdate.

For day -it should show sum sales for that particular day.

For mtd-if you select 20/01/2014 ,it should show sum of sales from start of that month to 20/01/2014 i.e selected date.

for ytd- if you select  20/01/2014 it should get the sum of sales form yearstart of date to selected date.

if i select any date form caledar object ,you need to select mtd ,ytd,day list box for sum of sales for ytd,mtd,day

[Anonymous]

I have attached the qvd in the question (top of page)

Thanks

[Anonymous]

Hi Kavitha,

I have corrected the logic in the script

Thanks for your help

[kavita25]

Can u share the script...or qvw...

[Anonymous · Accepted solution]

Hi

Ignore above script beacause fields has changed by me

Below code works fine

Let varMinDate = Floor(WeekStart(Date#('01/01/2014', 'DD/MM/YYYY')));

Let varMaxDate = Floor(WeekEnd(Date#('31/07/2015', 'DD/MM/YYYY')));

Cal:

LOAD

//$(varMinDate) + Iterno()-1 As Num,

Date($(varMinDate) + IterNo() - 1) as CalDate

AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

left Join

load CalDate as TempDate

Resident Cal;

NoConcatenate

Time:

load *,'MTD' As TimeGranule

Resident Cal

Where TempDate >= MonthStart(CalDate) and TempDate <= CalDate;

load *,'YTD' As TimeGranule

Resident Cal

Where TempDate >= YearStart(CalDate) and TempDate <= CalDate;

//Bringing Accumulated Day granule

Load * , 'Day' as TimeGranule

Resident Cal

where TempDate=CalDate;

//

CAL:

Load

      TempDate  ,

    Month( TempDate ) as SALES_MONTH,

    Year( TempDate ) as SALES_YEAR,

    MonthName( TempDate ) as SALES_MONTHNAME,

    'Q' & Ceil(Month( TempDate )/3) AS SALES_QUARTER,

    DayNumberOfYear(TempDate) as DayNumberOfYear,

    Day( TempDate ) as DayNumberOfMonth,

    DayNumberOfQuarter(TempDate) as DayNumberOfQuarter,

    num(Month( TempDate )) as MonthNum

    Resident Time;

    Drop Table Cal;