Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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.
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.