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.

14 Replies
simongoodman
Creator
Creator
Author

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

jvitantonio
Luminary Alumni
Luminary Alumni

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)

JV

simongoodman
Creator
Creator
Author

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

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)

simongoodman
Creator
Creator
Author

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