Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
simongoodman
Creator
Creator

Last Year MTD Issue using Flags

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.

1 Solution

Accepted Solutions
jvitantonio
Luminary Alumni
Luminary Alumni

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)

View solution in original post

14 Replies
jvitantonio
Luminary Alumni
Luminary Alumni

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


simongoodman
Creator
Creator
Author

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

jvitantonio
Luminary Alumni
Luminary Alumni

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

simongoodman
Creator
Creator
Author

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

Anonymous
Not applicable

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

jvitantonio
Luminary Alumni
Luminary Alumni

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

jvitantonio
Luminary Alumni
Luminary Alumni

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

Anonymous
Not applicable

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)."

simongoodman
Creator
Creator
Author

Hi JV

I will send a sample app ASAP.

Thanks

Simon