Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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
Whast the format for your "Date" field, mine is 'YYYYMM'
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)
=SUM({<YRMTH = {"$(=Date(Today(),'YYYYMM'))"}>}Sales)