MTD

[gautik92]

How to calculate the MTD value

[gautik92]

for Previous MTD

[PrashantSangle]

Hi,

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

Regards

[perumal_41]

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

[#5]

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:

[#6]

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)

[#7]

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