help with MTD & YTD in script

[Mario1011]

Hi All,

Very basic help needed.

How would I set up MTD & YTD in my script?

If my user selects a Month - it should give the MTD for that month and YTD up to and including the Month selected in the same table, etc

cal:
load *, month("Date") as Month, year("Date") as Year;
load date('1/07/2019' + recno() - 1) as "Date"
autogenerate '30/06/2020' - '1/07/2019';

[Vegar]

It's hard to script å master calendar that supports all possible ytd mtd qtd wtd etc. So I would recommend you to start investigating how to do it in the expressions on top of a normal master calendar. The keyword here is SET analysis.

Take a look at this post for samples of ytd and mtd calculations: Date Level Analysis - WTD, MTD, QTD & YTD (Current Year & Previous Year)

[shivanisapkale]

Set analysis for ytd:

{<Date={">=$(=date#(yearstart(max(Date)),'MM/DD/YYYY'))<=$(=date(max(Date),'MM/DD/YYYY'))"}>}

Set analysis for mtd:

{<Date={">=$(=date#(monthstart(max(Date)),'MM/DD/YYYY'))<=$(=date(max(Date),'MM/DD/YYYY'))"}>}

[Mario1011]

Thanks Shivanisapkale,

Unfortunately, it is still not working - as the YTD gives me the same answer as MTD when i select a given month

Could this be because my data is date driven? i.e I have a budget for the Full Year (01/07/2019, 01/08/2019, 01/09/2019, 01/10//2019, etc...) and actuals that have come through are only up to August ??

[shivanisapkale]

Hi,

Can you attach your sample data set so it will give a clear idea.

Regards,

Shivani sapkale