topic Re: Closed two month period flag in load script in QlikView

Closed two month period flag in load script

Anonymous — Wed, 16 Dec 2015 12:42:48 GMT

Good day!

in load script i made flag for last closed month name:

month(Addmonths(Today()-1, -1))

Now i need to make flag (0/1), which will filter data for last closed 2 month period.

Such as:

if(month(Addmonths(Today()-1, -1))='Jan',

here must be Jan (for all years)

if(month(Addmonths(Today()-1, -1))='Feb'

here must be Jan-Feb (for all years),

if(month(Addmonths(Today()-1, -1))='Mar',

here must be Feb-Mar (for all years)

if(month(Addmonths(Today()-1, -1))='Apr',

here must be Mar-Apr (for all years)

if(month(Addmonths(Today()-1, -1))='May',

here must be Apr-May (for all years)

if(month(Addmonths(Today()-1, -1))='Jun',

here must be May-Jun (for all years)

if(month(Addmonths(Today()-1, -1))='Jul',

here must be Jun-Jul (for all years)

if(month(Addmonths(Today()-1, -1))='Aug',

here must be Jul-Aug (for all years)

if(month(Addmonths(Today()-1, -1))='Sep',

here must be Aug-Sep (for all years)

if(month(Addmonths(Today()-1, -1))='Oct',

here must be Sep-Oct (for all years)

if(month(Addmonths(Today()-1, -1))='Nov',

here must be Oct-Nov (for all years)

if(month(Addmonths(Today()-1, -1))='Dec',

here must be Nov-Dec (for all years)

))))))))))))

Please, help!)

Re: Closed two month period flag in load script

MK_QSL — Wed, 16 Dec 2015 12:58:51 GMT

Try

If(Month(AddMonths(Today(),-2)) = 'Jan') for 2 months before and -1 for one month before

Re: Closed two month period flag in load script

rubenmarin — Wed, 16 Dec 2015 12:59:13 GMT

Hi, this post can help you: The As-Of Table

Check the example with the x-month rolling , I think you're asking about 2-months rolling, logic is the same.

Re: Closed two month period flag in load script

Anonymous — Wed, 16 Dec 2015 13:04:19 GMT

Thanks.

but i need following flag, for exaple, if last closed month is Jan

if(month(Addmonths(Today()-1, -1))='Jan',

if(condition to get 'Jan' for all years, 1, 0)

....

Re: Closed two month period flag in load script

MK_QSL — Wed, 16 Dec 2015 13:09:02 GMT

Provide some sample data along with expected output in excel file..

Re: Closed two month period flag in load script

Anonymous — Wed, 16 Dec 2015 13:18:25 GMT

In attachment..

Re: Closed two month period flag in load script

MK_QSL — Wed, 16 Dec 2015 13:23:25 GMT

Do you want Jan 2014 and 2015 both?

Are you interested two month back or one month back?

Re: Closed two month period flag in load script

Anonymous — Wed, 16 Dec 2015 13:27:09 GMT

1. Yes, for two years. But this is test data, in my real model there're 2015, 2014, 2013 ... Jan for all years

2. One month back only for Jan (if(month(Addmonths(Today()-1, -1))='Jan'). For all others - two month back.

Thanks.

Re: Closed two month period flag in load script

Kushal_Chawda — Wed, 16 Dec 2015 13:38:31 GMT

see the below link , this will help to create the flags

Rolling N months

Re: Closed two month period flag in load script

MK_QSL — Wed, 16 Dec 2015 13:38:58 GMT

Data:
Load
  *,
  Month(Date) as Month,
  If(Month(Date) = Month(AddMonths(Today(),-2)),1,0) as Temp_Flag
Inline
[
  Date, Sales
  01.01.2015, 5454
  02.01.2015, 54
  01.01.2015, 234
  01.01.2014, 634
  02.01.2014, 23
  01.01.2014, 64
  01.02.2015, 5454
  02.02.2015, 54
  01.02.2015, 234
  01.02.2014, 634
  02.02.2014, 23
  01.02.2014, 64
  01.03.2015, 100
  02.03.2015, 110
  01.02.2015, 50
  01.03.2014, 40
  02.03.2014, 60
  01.03.2014, 65
  01.04.2015, 44
  02.04.2015, 43
  01.04.2015, 20
  01.04.2014, 10
  02.04.2014, 5
  01.04.2014, 25
];


Final:
Load Date,Month, Sales, If(RowNo()=1,Temp_Flag, If(Temp_Flag=1 and Previous(Temp_Flag)=0, Temp_Flag, Peek('Flag'))) as Flag Resident Data Order By Month Desc, Date Desc;


Drop Table Data;


Now you can use
=SUM({<Flag = {1}>}Sales)

Re: Closed two month period flag in load script

rubenmarin — Wed, 16 Dec 2015 14:09:51 GMT

Not sure about what you want, it's based on current date?, so if today is 16 december you want to flag all october and november dates?

In that case this script seems work:

Let vNumMonthYesterday = Num(Month(AddMonths(Today()-1, -1)));

LOAD Date,

Sales,

If(Match($(vNumMonthYesterday)-Num(Month(Date)), 0, 1), 1, 0) as Flag

FROM

[.\2 closed Months.xlsx]

(ooxml, embedded labels, table is Лист1);

Also it's supposed that only flags Jan when Jan is last closed month but I didn't tested that.

Re: Closed two month period flag in load script

Anonymous — Wed, 16 Dec 2015 14:29:41 GMT

in your solution flag has only one value: 0...

Re: Closed two month period flag in load script

rubenmarin — Wed, 16 Dec 2015 14:39:28 GMT

If shows only 0 is because you don't have data for october or november, or because the date format is different than from the Excel, if you add data to november and october you can see the '1'.

Re: Closed two month period flag in load script

Anonymous — Wed, 16 Dec 2015 14:45:48 GMT

Thanks!

Will it works for others condition? For example, if last closed month is Jan, it must filter data for Jan, if last closed month in Feb: Jan-Feb?

if(month(Addmonths(Today()-1, -1))='Jan',

here must be Jan (for all years)

if(month(Addmonths(Today()-1, -1))='Feb'

here must be Jan-Feb (for all years),

if(month(Addmonths(Today()-1, -1))='Mar',

here must be Feb-Mar (for all years)

if(month(Addmonths(Today()-1, -1))='Apr',

here must be Mar-Apr (for all years)

if(month(Addmonths(Today()-1, -1))='May',

here must be Apr-May (for all years)

if(month(Addmonths(Today()-1, -1))='Jun',

here must be May-Jun (for all years)

if(month(Addmonths(Today()-1, -1))='Jul',

here must be Jun-Jul (for all years)

if(month(Addmonths(Today()-1, -1))='Aug',

here must be Jul-Aug (for all years)

if(month(Addmonths(Today()-1, -1))='Sep',

here must be Aug-Sep (for all years)

if(month(Addmonths(Today()-1, -1))='Oct',

here must be Sep-Oct (for all years)

if(month(Addmonths(Today()-1, -1))='Nov',

here must be Oct-Nov (for all years)

if(month(Addmonths(Today()-1, -1))='Dec',

here must be Nov-Dec (for all years)

))))))))))))

Re: Closed two month period flag in load script

rubenmarin — Wed, 16 Dec 2015 15:45:25 GMT

It should work but I don't have time now to test, change "Today()" (in the variable) to the date you want to test to confirm it's working in all cases.

Re: Closed two month period flag in load script

Anonymous — Thu, 17 Dec 2015 06:14:33 GMT

Thanks!!! it works!

Dear, tell me please, is there in your expression possibility to make change for closed month Jan: get Jan'Current year-Dec'Previous Year?

Thanks.

Re: Closed two month period flag in load script

rubenmarin — Thu, 17 Dec 2015 07:58:58 GMT

You can add a condition to the 'If' to check if it's january and date is december:

Let vNumMonthYesterday = Num(Month(AddMonths(Today()-1, -1)));

LOAD Date,

Sales,

If($(vNumMonthYesterday)=1 and Num(Month(Date))=12, 1,

If(Match($(vNumMonthYesterday)-Num(Month(Date)), 0, 1), 1, 0)) as Flag

FROM

[.\2 closed Months.xlsx]

(ooxml, embedded labels, table is Лист1);

Re: Closed two month period flag in load script

Anonymous — Thu, 17 Dec 2015 08:26:50 GMT

Thanks!

i've try it.

it gives me also jan for 2014. But i need jan only forcurrent year and dec for previous.

Re: Closed two month period flag in load script

rubenmarin — Thu, 17 Dec 2015 08:58:02 GMT

You can modify the 'If' to fit your requirements checking conditions for Year(Today())=Year(Date) or Year(Today())=Year(Date)-1

Re: Closed two month period flag in load script

Anonymous — Thu, 17 Dec 2015 09:06:25 GMT

please, can you write example?

Thanks