Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have used flags in the script to create a number of periods. The Last Year MTD is not working as expected. For example if the current MTD runs 28-07-2014 to 01-08-2014, Last Year MTD selects the dates 28-07-2013 to 30-08-2013 (The full month).
In the script
//Creating variables element. ‘TodayValues’ is tmp table that takes calendar data from excel.
LET vToday = TODAY(); LET vTodayFinYear = PEEK('Financial Year',-1,'TodayValues');
LET vTodayMonth = Peek('Accounting Month',-1,'TodayValues');
//In Master Calendar element
If([Financial Year] = $(vTodayFinYear)-1 and [Accounting Month]= $(vTodayMonth), 1, 0) as Cal.IsthisPeriodLastYear,
if([Financial Year] = $(vTodayFinYear) and [Accounting Month] = $(vTodayMonth), 1, 0) as Cal.IsThisPeriod,
Why does this happen and what is the solution? Any help would be appreciated.
Hi Simon,
I don't have your excel file to reload the app, but you can add this:
If([Financial Year] = $(vTodayFinYear)-1 and [Accounting Month]= $(vTodayMonth) AND PostingDate <=
addmonths(today(),-12), 1, 0)
Hello simon,
This is happening because you are comparing only the months and not the days.
In your first expression:
If([Financial Year] = $(vTodayFinYear)-1 and [Accounting Month]= $(vTodayMonth), 1, 0) as Cal.IsthisPeriodLastYear
you are marking with "1" the whole month of august of 2013 (knowing that today is Aug 1st 2014).
What you should do is the following:
LOAD
IF(DATE >= monthstart(today()) and DATE <= TODAY()), 1,0) as currentMTD,
IF(DATE >= ADDMONTHS(monthstart(TODAY()), -12) and DATE <=ADDMONTHS(TODAY(),-12), 1,0) as LastYearMTD
;
JV
JV thanks for the explanation. I do not think solution will work in this case because the start of the current month is the 28-07-2014, i.e. a Monday and for Last Year the 29-07-2013. So the working month ('Acounting Month')shifts around. That is why I loaded in the original excel calendar. Can "If([Financial Year] = $(vTodayFinYear)-1 and [Accounting Month]= $(vTodayMonth), 1, 0) as Cal.IsthisPeriodLastYear" be amended? Simon
Hi Simon, I see.
you can try something like this:
"If([Financial Year] = $(vTodayFinYear)-1 and [Accounting Month]= $(vTodayMonth) AND FiscalDate <= makedate($(vTodayFinYear)-1, $(vTodayMonth), day(today()) , 1, 0)
JV
Hi JV
Your earlier version "If([Financial Year] = $(vTodayFinYear)-1 and [Accounting Month]= $(vTodayMonth) AND day(FinancialDate) = day(today()-1), 1, 0) as Cal.IsthisPeriodLastYear" just returned one date .based on = day(today()-1) which makes sense to me.
With "If([Financial Year] = $(vTodayFinYear)-1 and [Accounting Month]= $(vTodayMonth) AND FiscalDate <= makedate($(vTodayFinYear)-1, $(vTodayMonth), day(today()) , 1, 0)" it would not run successfully. The Error message read "Error in Expression: MakeDate takes 1-3 parameters. Have you any ideas?
Thanks
Simon
Simon,
There is nothing to do with Day here. I believe the Accounting Month is already linking with month start and month end date in calendar. MTD flags looks fine to me for both last year and current year. There could be something else which is causing issue. If possible provide your app with scrambled data to look into it.
Thanks
Hi Simon, it's easier if you can give me the app or at least a sample app. the error seems to be in the expression but I'm not sure how you are using.
JV
Hi Satyadev,
How can you know that things are linked in a calendar table if you don't even see the application?
On the other hand the problem is indeed with the day. If you take a look at his expression, he's returning the whole month of current month (Aug) but for last year, but he actually wants to return MTD last year. If today is Aug 4, he wants from July 27th 2013 to Aug 4th 2013.
JV
Hi,
I might be wrong but please read Simon's statement below. He is looking for last year full month data not till Aug 4th 2013.
"For example if the current MTD runs 28-07-2014 to 01-08-2014, Last Year MTD selects the dates 28-07-2013 to 30-08-2013 (The full month)."
Hi JV
I will send a sample app ASAP.
Thanks
Simon