Month to date (MTD) in loading script

[razvan_brais]

Hey guys. I have a question.

I would like to create a table in which I want to have values for Month to Date. I know it can be achieved , by using expresions , but it would be better if I will have this table created.

Can you please help me with some hints?

Thank you,

Razvan

[tresesco]

Have a look here: Year-over-Year Comparisons

[razvan_brais]

This is achieved with expressions . But I would like to do this in loading script.

[tresesco]

You mean, the value itself has to be calculated in the script? And you don't even want to use flag in set analysis expression? If so, I guess that would not be very good approach to follow since that makes your values literally static.

[rahulpawarb]

May be this:

SummaryTable:
LOAD Year([Date]) AS CYYear,
     Month([Date]) AS CYMonth,
     Sum(Sales) AS Amount
Resident Orders
Group By Year([Date]),
         Month([Date]);

Regards!

Rahul

[razvan_brais]

This is good , if I want to have sa static value for MTD.

Basically , I want to calculate for each day the value MTD.

[tresesco]

May be the way is: The As-Of Table

[tresesco]

Check it here, I have implemented MTD in the script with a sample data set.

Tran:

Load

       *,

       month(Date) as Month,

       year(Date) as Year;

Load

       RecNo() as Sales,

       date(Addyears(today(),-2)+RecNo()) as Date

AutoGenerate 365*2;

NoConcatenate

AsOfCal:

load

       *

Resident Tran;

JOIN (AsOfCal)

Load

       Date as AsOfDate

Resident Tran;

Drop Table Tran;

LEFT JOIN

Load

       AsOfDate as Date,

       Sum(If(InMonthToDate(Date,AsOfDate,0),Sales)) as MTDSales

resident AsOfCal  Group By AsOfDate;

PFA