CALC MONTH TO DATE FROM YEAR TO DATE

[#1]

I am trying to calculate Month To Date data (MTD), I already have Year To Date (YTD) Data coming from the source.  Can you please help

Here is the sample data

YRMTH	YR	MTH	Sales (YTD)
201507	2015	Jul	20
201507	2015	Jul	30
201506	2015	Jun	40
201505	2015	May	55
201506	2015	Jun	65
201501	2015	Jan	75
201501	2015	Jan	80
201504	2015	Apr	90
201507	2015	Jul	95
Sales Data is Cumulative

[satishkurra]

Please find below the script for MTD and previous year MTD

MTD

=Sum({$<Date={">=$(=Num(MonthStart(Max(Date))))<=$(=Max(Date))"}>}Sales)

MTD-1

=Sum({$<Date={">=$(=Num(MonthStart(Max(Date),-12)))<=$(=AddYears(Max(Date),-1))"}>}Sales)

Date is the transaction date

[#3]

Whast the format for your "Date" field, mine is 'YYYYMM'

[satishkurra]

Ok in that case first in scripting window, set your date format to YYYYMM and then no need to change in the above expression.

Or if youthink the expression only requires the change perform below

MTD

=Sum({$<Date={">=$(=Num(MonthStart(Max($(=Date(Date,'YYYYMM'))))))}>}Sales)

[MK_QSL]

=SUM({<YRMTH = {"$(=Date(Today(),'YYYYMM'))"}>}Sales)