topic Re: MTD in QlikView

MTD

gautik92 — Mon, 03 Nov 2014 09:12:12 GMT

How to calculate the MTD value

Re: MTD

ali_hijazi — Mon, 03 Nov 2014 09:15:48 GMT

can you please explain in english what do you mean by mtd?

Re: MTD

gautik92 — Mon, 03 Nov 2014 09:16:26 GMT

Month to Date Value Calculation

Re: MTD

its_anandrjs — Mon, 03 Nov 2014 09:17:01 GMT

Calculate MTD:

=Sum({<DateField={'>=$(=MonthStart(Today()))<=$(=Today())'}>} Metric)

Ex:-

=Sum({<DateField={'>=$(=MonthStart(Today()))<=$(=Today())'}>} SAles)

Re: MTD

gautik92 — Mon, 03 Nov 2014 09:21:56 GMT

Actually This s my YTD

=sum({<Year={$(=Max(Year))},Transactiondate={"<=$(=max(Transactiondate))"}>} Qty)

can i modify this for MTD

Re: MTD

its_anandrjs — Mon, 03 Nov 2014 09:26:11 GMT

Why not you use another expression and you can try this expression

=Sum({<DateField={'>=$(=MonthStart(Today()))<=$(=Today())'}>} Metric)

Or you can try

=sum({<MonthField = {$(=Max(MonthField))},Transactiondate={"<=$(=max(Transactiondate))"}>} Qty)

Regards

Anand

Re: MTD

Mon, 03 Nov 2014 09:26:30 GMT

=sum({<Year={$(=Max(Year))},Transactiondate={">=$(=MonthStart(max(Transactiondate)))"}>} Qty)

Re: MTD

PrashantSangle — Mon, 03 Nov 2014 09:31:26 GMT

Hi,

Try like

=sum({<Year={$(=Max(Year))},Transactiondate={"<=$(=max(Transactiondate))>=$(=monthstart(max(Transactiondate)))"}>} Qty)

=sum({<Year={$(=Max(Year))},Transactiondate={"<=$(=date(max(Transactiondate)))>=$(=date(monthstart(max(Transactiondate))))"}>} Qty)

=sum({<Year={$(=Max(Year))},Transactiondate={"<=$(=timestamp(max(Transactiondate)))>=$(=timestamp(monthstart(max(Transactiondate))))"}>} Qty)

Regards

Re: MTD

Mon, 03 Nov 2014 09:37:15 GMT

hi gowtham,

yes, you can modify your ytd expression to your mtd expression. just replace year with month. i.e.

=sum({<Month={$(=Max(Month))},Year={$(=Max(Year))},Transactiondate={"<=$(=max(Transactiondate))"}>}Qty)

cheers,

kunal bhattacharjee

Re: MTD

perumal_41 — Mon, 03 Nov 2014 09:38:01 GMT

Try this

=sum({<Year={$(=Max(Year))},Month={'$(=max(Month))'},Transactiondate={"<=$(=max(Transactiondate))"}>} Qty)

Re: MTD

gautik92 — Mon, 03 Nov 2014 09:39:13 GMT

for Previous MTD

Re: MTD

PrashantSangle — Mon, 03 Nov 2014 09:43:31 GMT

Hi,

=sum({<Year={$(=Max(Year))},Transactiondate={">=$(=MonthStart(Addmonths(max(Transactiondate),-1)))"}>} Qty)

Regards

Re: MTD

perumal_41 — Mon, 03 Nov 2014 09:44:05 GMT

=sum({<Year={$(=Max(Year))},Transactiondate={">=$(=MonthStart(addmonths(max(Transactiondate),-1))),<=$(=MonthEnd(addmonths(max(Transactiondate),-1)))"}>} Qty)

Re: Re: MTD

Mon, 03 Nov 2014 09:48:27 GMT

MonthStart function returns first date of month.

As I imagine , if you have to compare the same data bank for the previous year, you should add an upper limit to the function range:

MonthStart(Max(DATE)) ---> Max(Transactiondate)

However, you should generate all the variables you need for your "time analysis" directly in the load script.

You can do this by loading a temporary table and then generate all the variables using peek function, here is an example:

Re: MTD

Mon, 03 Nov 2014 11:29:36 GMT

If you need to analyze the same MTD period in previous month you can use:

=sum({<Year={$(=Max(Year))},Transactiondate={">=$(=AddMonths(MonthStart(max(Transactiondate)),-1)) <= $(=AddMonths(max(Transactiondate)),-1)"}>} Qty)

Otherwise, if you need do calculate total sum for previous month you can use:

=sum({<Year={$(=Max(Year))},Transactiondate={">=$(=MonthStart(AddMonths(max(Transactiondate),-1))) <= $(=MonthEnd(AddMonths(max(Transactiondate),-1)))"}>} Qty)

Re: MTD

Mon, 03 Nov 2014 11:35:38 GMT

Anyway, I don't understand why you set year field before the ecplicit date limitation (which already contains implicitly the year limitation).

You should remove it i think