alternative period for each month

[#1]

Hi All,

I would like to use an alternative month period.

For example I have outgoings on daily basis but if I receive an event before the 10th of the month I would like to take it into account in the previous month except if this day (10th day's of the month) is a weekday, in that case I would like to use the previous Friday.

feb 01. feb.02 feb.03 .... feb 09 should go to jan and feb 10, feb 11 .... marc 09 should go to feb and so on (except 10th is weekend...)

I made a Mastercalendar and a new calc flag field in the backend and used set analysis in the frontend, but this doesn't handle weekdays and I think this problem can be solved in a better way.

If(If(Month(Date(McDate-9))<>Month(McDate),Month(McDate) 1,Month(McDate))=0,'December', If(Month(Date(McDate-9))<>Month(McDate),Month(McDate)-1,Month(McDate)))

as McAlternativeMonthFlag

Thanks,

Dan

[migueldelval]

Hi Daniel,

Check this example and tell me please if it´s correct.

Regards

Miguel del Valle

[#3]

Hi Miguel,

could you please attach the script in a text file, I don't have qlik licence.

Regards,

Daniel

[migueldelval]

Hi Daniel,

My script-example:

Date:

load * inline [

Date

21/05/2017,

10/05/2017,

03/05/2017,

05/06/2017,

22/06/2017

];

Date2:

Load

Date,

If(day(Date)<=10 and weekday(Date)='lu.' or day(Date)<=10 and weekday(Date)='ma.' or day(Date)<=10 and weekday(Date)='mi.' or day(Date)<=10 and weekday(Date)='ju.' or day(Date)<=10 and weekday(Date)='vi.',

(Month(AddMonths(Date,-1))),Month(Date)) as FlagMonth,

month(Date) as MonthDate

resident Date;

And you can see:

Regards

Miguel del Valle

[#5]

Hi Miguel,

I copied pasted your solution and it is not good, please check it.

for example 2017.01.01 have JAN flag but should have dec since it is before 10th.

SET ThousandSep=' ';

SET DecimalSep=',';

SET MoneyThousandSep=' ';

SET MoneyDecimalSep=',';

SET MoneyFormat='# ##0,00 Ft;-# ##0,00 Ft';

SET TimeFormat='h:mm:ss';

SET DateFormat='YYYY.MM.DD';

SET TimestampFormat='YYYY.MM.DD h:mm:ss[.fff]';

SET FirstWeekDay=0;

SET BrokenWeeks=1;

SET ReferenceDay=0;

SET FirstMonthOfYear=1;

SET CollationLocale='hu-HU';

SET MonthNames='jan.;febr.;márc.;ápr.;máj.;jún.;júl.;aug.;szept.;okt.;nov.;dec.';

SET LongMonthNames='január;február;március;április;május;június;július;augusztus;szeptember;október;november;december';

SET DayNames='H;K;Sze;Cs;P;Szo;V';

SET LongDayNames='hétfő;kedd;szerda;csütörtök;péntek;szombat;vasárnap';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Let vMinDate = Num(Date('2017.01.01'));

Let vMaxDate = Num(Date('2017.05.15'));

[Master Calendar]:

Load *,

If( !

  day(McDate)<=10 and weekday(McDate)='Mon'

  or day(McDate)<=10 and weekday(McDate)='Tue'

  or day(McDate)<=10 and weekday(McDate)='Wed'

  or day(McDate)<=10 and weekday(McDate)='Thu'

  or day(McDate)<=10 and weekday(McDate)='Fri',

  (Month(AddMonths(McDate,-1))),Month(McDate)) as FlagMonth,

  AutoNumber(Year & Quarter, 'QuarterID') as [QuarterID],

  AutoNumber(Period, 'PeriodID') as [PeriodID],

    monthstart(McDate, 9) as McMonthStart

    ,

    date(McDate-9) as TestDay,

    Month(Date(McDate-9)) as TestMonth

    ;

LOAD DISTINCT

  Year(Temp_Date) * 100 + Month(Temp_Date) as [Period],

    Year(Temp_Date)  as [Year],

    Date(Temp_Date)  as [McDate],

  Month(Temp_Date) as [Month],

  Date(Temp_Date, 'YYYY-MM')  as [Year - Month],

  'Q' & Ceil(Month(Temp_Date) / 3)  as [Quarter]

  ;

LOAD DISTINCT

  $(vMinDate) + IterNo() - 1  as Temp_Date

AUTOGENERATE (1)

WHILE $(vMinDate) + IterNo() - 1 <= $(vMaxDate)

;

[migueldelval]

Hi Daniel,

1st of January it´s in January because is Sunday. My condition says that all days<10 and network day. If you want explain me your requirement other time and I will help you.

+

Regards

Miguel del Valle

[#7]

Miguel,

it was my mistake sorry.

Regards,

Daniel

[migueldelval]

Hi Daniel,

Please close your question if it´s answered to help others.

Regards

Miguel del Valle

[#9 · Accepted solution]

Hi,

meanwhile I solved:

If(Day(McDate)=9 and weekday(McDate)='6',(Month(AddMonths(McDate,-1))),

  If(Day(McDate)=8 and weekday(McDate)='5',(Month(AddMonths(McDate,-1))),

  If(Day(McDate)=10 and weekday(McDate)='6',Month(McDate),

  If(Day(McDate)=9 and weekday(McDate)='5',Month(McDate),

  If(Day(McDate)=8 and weekday(McDate)='4',Month(McDate),

  If(Day(McDate)=9 and weekday(McDate)='4',Month(McDate),

  If(Day(McDate)<='9',(Month(AddMonths(McDate,-1))),Month(McDate))))))))as FlagMonth2,

Dan