Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
simongoodman
Contributor

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.

Tags (3)
1 Solution

Accepted Solutions
Luminary
Luminary

Re: Last Year MTD Issue using Flags

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
Luminary
Luminary

Re: Last Year MTD Issue using Flags

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
Contributor

Re: Last Year MTD Issue using Flags

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

Luminary
Luminary

Re: Last Year MTD Issue using Flags

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
Contributor

Re: Last Year MTD Issue using Flags

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

satyadev_j
Valued Contributor

Re: Last Year MTD Issue using Flags

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

Luminary
Luminary

Re: Last Year MTD Issue using Flags

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

Luminary
Luminary

Re: Last Year MTD Issue using Flags

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

satyadev_j
Valued Contributor

Re: Last Year MTD Issue using Flags

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
Contributor

Re: Last Year MTD Issue using Flags

Hi JV

I will send a sample app ASAP.

Thanks

Simon