topic Re: Month to date (MTD) in loading script in QlikView

Month to date (MTD) in loading script

razvan_brais — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Month to date (MTD) in loading script

tresB — Fri, 28 Jul 2017 05:45:28 GMT

Have a look here: Year-over-Year Comparisons

Re: Month to date (MTD) in loading script

razvan_brais — Fri, 28 Jul 2017 05:48:56 GMT

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

Re: Month to date (MTD) in loading script

tresB — Fri, 28 Jul 2017 05:52:14 GMT

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.

Re: Month to date (MTD) in loading script

rahulpawarb — Fri, 28 Jul 2017 06:00:04 GMT

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

Re: Month to date (MTD) in loading script

razvan_brais — Fri, 28 Jul 2017 06:31:37 GMT

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

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

Re: Month to date (MTD) in loading script

tresB — Fri, 28 Jul 2017 06:49:29 GMT

May be the way is: The As-Of Table

Re: Month to date (MTD) in loading script

tresB — Fri, 28 Jul 2017 10:05:18 GMT

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