Calculate MTD & YTD with the Field MonthYear

[piyush_s11]

Hi Friends,

I wanted to calculate the MTD & YTD. I have two fields Month & Year so I have created a third field as MonthYear. Format of this third field is Jun-2018. I don't have date field. Now I wanted to calculate MTD & YTD with the field MonthYear. Request you to help e in this.

Thanks.

@avinashelite , @kushalthakral  @tresesco @prabir_c 

[eliran]

Hi,

I'm not sure what is your need, do you want to get a set analysis expression? add MTD / YTD flag into the script?

If you have only month and year, what do you consider MTD? is it current month?

First, when you combine the month year, do it with function makedate, so you can have a date afterall

MakeDate(YearField,MonthField,1) - will result having set of dates when month start, this way you can always use date calculations if you wish.

Load

MonthField,

YearField,

date(MakeDate(YearField,MonthField,1),'YYYY-MMM') as YearMonth,

if(num(MonthField)<=num(month(Today())),1,0) as YTDFlag,

if(num(MonthField)=num(month(Today())),1,0) as MTDFlag, 

//version 2 - if you want only current year data

if(num(MonthField)<=num(month(Today())) and YearField=Year(Today()),1,0) as YTDFlag,

if(num(MonthField)=num(month(Today())) and YearField=Year(Today()),1,0) as MTDFlag, 

sum({<YTDFlag={1}>}Sales)

sum({<MTDFlag={1}>}Sales)

I hope it helps,

Eliran.

[tresesco · Accepted solution]

Create a date field in the script to handle anything related to date/calendar. Try like:

Date(Date#(Month&Year, 'MMMYYYY')) as Date    // assuming your month field is in MMM format

Then you can use Date field in the UI for YTD/MTD calculations. If you still have doubt, try to share a sample app explaining the expected output format. We will give a shot to help you.