topic Re: Last Year MTD Issue using Flags in QlikView

Last Year MTD Issue using Flags

simongoodman — Fri, 01 Aug 2014 13:30:39 GMT

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.

Re: Last Year MTD Issue using Flags

jvitantonio — Fri, 01 Aug 2014 14:15:44 GMT

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

;

Re: Last Year MTD Issue using Flags

simongoodman — Fri, 01 Aug 2014 14:48:38 GMT

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

Re: Last Year MTD Issue using Flags

jvitantonio — Fri, 01 Aug 2014 15:27:13 GMT

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)

Re: Last Year MTD Issue using Flags

simongoodman — Mon, 04 Aug 2014 08:45:31 GMT

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

Re: Last Year MTD Issue using Flags

Anonymous — Mon, 04 Aug 2014 09:27:39 GMT

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

Re: Last Year MTD Issue using Flags

jvitantonio — Mon, 04 Aug 2014 09:42:27 GMT

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.

Re: Last Year MTD Issue using Flags

jvitantonio — Mon, 04 Aug 2014 09:45:40 GMT

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.

Re: Last Year MTD Issue using Flags

Anonymous — Mon, 04 Aug 2014 09:57:46 GMT

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

Re: Last Year MTD Issue using Flags

simongoodman — Mon, 04 Aug 2014 11:50:31 GMT

Hi JV

I will send a sample app ASAP.

Thanks

Simon

Re: Re: Last Year MTD Issue using Flags

simongoodman — Mon, 04 Aug 2014 14:23:51 GMT

Hi JV

Attached is a sample qvw. In the script I have commented out your suggestion so there is data loaded. If you comment out my original script and try yours you will get the error as mentioned.

// If([Financial Year] = $(vTodayFinYear)-1 and [Accounting Month]= $(vTodayMonth) AND Date <=$(vTodayFinYear)-1, $(vTodayMonth), day(today()), 1, 0) as Cal.IsthisPeriodLastYear,

In the qvw you can track the Cal.ISthisPeriodLastYear flag which runs 29-07-2013 and not 03-08-2013 as required.

For some reason this sample qvw has picked up '-' in date and assigned flags.

Thanks

Simon

Re: Last Year MTD Issue using Flags

jvitantonio — Mon, 04 Aug 2014 14:41:12 GMT

Hi Simon, could you please confirm what you want to see in MTD last year? (tell me exactly the dates last year for today's date)

Re: Last Year MTD Issue using Flags

simongoodman — Mon, 04 Aug 2014 15:13:51 GMT

Hi JV

Yes I am looking for MTD Last Year. This Years MTD with the sample should run 28-07-2014 to 04-07-2014. While the MTD LY should be 27-07-2013 to 04-08-2013 although I did not add any data for 27-07-2013 so the first posting date is 28-07-2013.

Simon

Re: Last Year MTD Issue using Flags

jvitantonio — Mon, 04 Aug 2014 16:14:26 GMT

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)

Re: Last Year MTD Issue using Flags

simongoodman — Thu, 07 Aug 2014 10:45:25 GMT

Think it has worked, although after some reloads MTD LY did not pick up the last date when compared to today. E.g. if Today = 07-08-14 then MTD LY should include 07-08-13. Many thanks for the help Simon