Fiscal Month Start in Expression

[johngouws]

Good morning.
I am asking for assistance with something that has occupied a few days of my time!
I have a field in my calendar called BaseMonth. I use it for quite a few things. Unfortunately I need to find a way of using it, or else something similar, in a App with a fisc Calendar where the first day of the month is the 26th.

Something else in the expression is that it returns the MTD values for the past 12 months.
In the included snippet this first object shows how it will normally work when the month starts on the 1st of the month. As today is the 9th the yellow highlights shows all the previous months to the same date.

What I am struggling with is the center object. The Mass value since the 1st day of the Fisc month, 26th to month end, should be included in the second column. I should then have the same layout as the 1st object but the Dates will range from the 26th to the 9th for each BaseMonth.

Below is the Calendar script and expression I normally use.

When Mth Start on 1st
BaseMonth Calendar script:
Year(Date)*12 + Month(Date) as [BaseMonth]

Expression:
sum({$<[Year]=,[Period]=,Quarter=,Month=
,Day={"<=$(=day(Today()))>=$(=day(monthstart(Today())) )"}
,BaseMonth={">=$(=(Year(Today())*12 + Month(Today())-13) )"}
>} Mass)


When Mth Start on 26th
BaseMonth Fiscal Calendar script:
(Year(YearStart(FiscalDate,0,$(FirstMonthOfYear))) )*12 + Num(Mod(Month(FiscalDate) - $(FirstMonthOfYear), 12)+1,'00') as [BaseMonth]
// Returns the correct BaseMonth / Date - See right side object

Expression:
I don't want to give all my attempts! They don't work. 

I really appreciate any assistance with this. 

Thank you. 

[marcus_sommer]

Do you have a separate fiscal calendar which contained all relevant fiscal period-fields? With it many of the usual calendar-stuff should work like with a normal calendar - some date-functions may need an appropriate offset-value or might be replaced with a lookup to the fiscal calendar which may not only have years/months/weeks in this context else also (cumulative) working-days, a lot of flags for MTD, LMTD and so on ...

Fiscal Year - Qlik Community - 1472103

How to use - Master-Calendar and Date-Values - Qlik Community - 1495741

- Marcus

[johngouws]

Thanks for the suggestions: 

I use a combination of those already - This 1st script is my standard calendar I use where months normally start on the 1st of the month. Works very well for me. 

[$(_calendar)]:
LOAD
[$(_field)]
,YearName([$(_field)],0,$(FirstMonthOfYear)) as [$(_prefix)YearName]
,(Year(YearStart([$(_field)],0,$(FirstMonthOfYear)))+1) as [$(_prefix)Year]
,Month([$(_field)]) as [$(_prefix)Month]
,date(monthstart([$(_field)])) as [$(_prefix)MonthStart]
,date(monthend([$(_field)])) as [$(_prefix)MonthEnd]
,YearStart([$(_field)],0,$(FirstMonthOfYear)) as [$(_prefix)YearStart]
,YearEnd([$(_field)],0,$(FirstMonthOfYear)) as [$(_prefix)YearEnd]
,(Year(YearStart([$(_field)],0,$(FirstMonthOfYear)))+1)&'-'&'Q' & Ceil(Month(addmonths(addyears([$(_field)],1),-2)) / 3) as [$(_prefix)YearQuarter]
,(Year(YearStart([$(_field)],0,$(FirstMonthOfYear)))+1)&'-'& Num(Mod(Month([$(_field)]) - $(FirstMonthOfYear), 12)+1,'00') as [$(_prefix)YearPeriod]
,inmonthtodate ([$(_field)], Today(), 0, $(FirstMonthOfYear) ) as [$(_prefix)inMTD]
,inmonthtodate ([$(_field)], Today(), -1, $(FirstMonthOfYear) ) as [$(_prefix)inPMTD]
,inyeartodate ([$(_field)], Today(), 0, $(FirstMonthOfYear)) as [$(_prefix)inYTD]
,inyeartodate ([$(_field)], Today(), -1, $(FirstMonthOfYear)) as [$(_prefix)inPYTD]
,inquartertodate ([$(_field)], Today(), 0, $(FirstMonthOfYear)) as [$(_prefix)inQTD]
,Num(Mod(Month([$(_field)]) - $(FirstMonthOfYear), 12)+1,'00') as [$(_prefix)Period]
,'Q' & CEIL(MONTH(addmonths(addyears([$(_field)],1),-2)) / 3) as [$(_prefix)Quarter]
,Div([$(_field)]-YearStart([$(_field)],0,$(FirstMonthOfYear)),7)+1 as [$(_prefix)Week]
,weekday([$(_field)]) as [$(_prefix)Weekday]
,day([$(_field)]) as [$(_prefix)Day]
,MonthName([$(_field)]) as [$(_prefix)MonthYear]
,Year([$(_field)])*12 + Month([$(_field)]) as [$(_prefix)BaseMonth]
;
// Generate range of dates between min and max.
LOAD
date(DateMin + IterNo()) as [$(_field)]
WHILE DateMin + IterNo() <= DateMax
;
// Find min and max of date field values.
LOAD
min(datefield)-1 as DateMin
,max(datefield) as DateMax
;
// Load date field values.
LOAD
FieldValue('$(_field)', RecNo()) as datefield
AutoGenerate FieldValueCount('$(_field)');

I am also ok with this results of the below calendar. The fundemental issue I am having is in the expression where I need to show the MTD values of the last 12 Fisc months. The expression shared at first is works when the month starts on the first. I cannot get it to work however when the fisc month starts on the 26th. 

[$(_calendar)]:
LOAD
//Fiscal Calendar based on mid month start
*
,YearName(FiscalDate,0,$(FirstMonthOfYear)) as [$(_prefix)YearName]
,(Year(YearStart(FiscalDate,0,$(FirstMonthOfYear)))+1) as [$(_prefix)Year]
,Month(monthstart(FiscalDate-$(LastDayofFiscYear),1)) as [$(_prefix)Month]

,if(month(FiscalDate) > 2,MakeDate((Year(YearStart(FiscalDate,0,$(FirstMonthOfYear)))), Num(Month(FiscalDate)-1),$(LastDayofFiscYear)+1)
, if(Month(FiscalDate)>1, MakeDate((Year(YearStart(FiscalDate,0,$(FirstMonthOfYear)))+1), Num(Month(FiscalDate)-1 ),$(LastDayofFiscYear)+1)
, MakeDate((Year(YearStart(FiscalDate,0, $(FirstMonthOfYear)))), 12, $(LastDayofFiscYear)+1))) as [$(_prefix)MonthStart]

,if(month(FiscalDate) > 2, MakeDate((Year(YearStart(FiscalDate,0,$(FirstMonthOfYear)))), Num(Month(FiscalDate)),$(LastDayofFiscYear))
,MakeDate((Year(YearStart(FiscalDate,0,$(FirstMonthOfYear)))+1), Num(Month(FiscalDate)),$(LastDayofFiscYear))) as [$(_prefix)MonthEnd]
,MakeDate((Year(YearStart(FiscalDate,0,$(FirstMonthOfYear)))),02,$(LastDayofFiscYear)+1) as [$(_prefix)YearStart]
,MakeDate((Year(YearEnd(FiscalDate,0,$(FirstMonthOfYear)))),02,$(LastDayofFiscYear)) as [$(_prefix)YearEnd]
,(Year(YearStart(FiscalDate,0,$(FirstMonthOfYear)))+1)&'-'&'Q' & Ceil(Month(addmonths(addyears(FiscalDate,1),-2)) / 3) as [$(_prefix)YearQuarter]
,(Year(YearStart(FiscalDate,0,$(FirstMonthOfYear)))+1)&'-'& Num(Mod(Month(FiscalDate) - $(FirstMonthOfYear), 12)+1,'00') as [$(_prefix)YearPeriod]
,inmonthtodate(FiscalDate, Today(), 0, $(FirstMonthOfYear) ) as [$(_prefix)inMTD]
,inmonthtodate(FiscalDate, Today(), -1, $(FirstMonthOfYear)) as [$(_prefix)inPMTD]
,inyeartodate(FiscalDate, Today(), 0, $(FirstMonthOfYear)) as [$(_prefix)inYTD]
,inyeartodate(FiscalDate, Today(), -1, $(FirstMonthOfYear)) as [$(_prefix)inPYTD]
,inquartertodate(FiscalDate, Today(), 0, $(FirstMonthOfYear)) as [$(_prefix)inQTD]
,Num(Mod(Month(FiscalDate) - $(FirstMonthOfYear), 12)+1,'00') as [$(_prefix)Period]
,'Q' & CEIL(MONTH(addmonths(addyears(FiscalDate,1),-2)) / 3) as [$(_prefix)Quarter]
,Div([$(_field)]-MakeDate((Year(YearStart(FiscalDate,0,$(FirstMonthOfYear)))),02,$(LastDayofFiscYear)+1),7)+1 as [$(_prefix)Week]
,weekday([$(_field)]) as [$(_prefix)Weekday]
,day([$(_field)]) as [$(_prefix)Day]
,MonthName([$(_field)]) as [$(_prefix)MonthYear]
,(Year(YearStart(FiscalDate,0,$(FirstMonthOfYear))) )*12 + Num(Mod(Month(FiscalDate) - $(FirstMonthOfYear), 12)+1,'00') as [$(_prefix)_BaseMonth]
;

LOAD
// Use FiscalDate in Fiscal Calendar ONLY - Can drop after loading calendar
Date(monthstart([$(_field)]-$(LastDayofFiscYear),1)) as FiscalDate,
[$(_field)]
;
// Generate range of dates between min and max.
LOAD
date(DateMin + IterNo()) as [$(_field)]
WHILE DateMin + IterNo() <= DateMax
;
// Find min and max of date field values.
LOAD
min(datefield)-1 as DateMin
,max(datefield) as DateMax
;
// Load date field values.
LOAD
FieldValue('$(_field)', RecNo()) as datefield
AutoGenerate FieldValueCount('$(_field)');

Thank you for actually making the suggestion. 

[marcus_sommer]

I'm not sure if:

...
Date(monthstart([$(_field)]-$(LastDayofFiscYear),1)) as FiscalDate,
...

and then applying normal date-functions on it is expedient because it means the is no real dates anymore else just monthly values - forced from the monthstart() - and then on top of it the flags and period-information are created with normal date-functions.

I must admit I never used a financial calendar in productive else just playing with various calendar-types some years ago to learn how they are working but I would tend to replicate a normal calendar structure. Means the first day in it is the 01/01/YYYY (independent from the offset-value between the calendars) and all fiscal month/weeks and flags/consecutive information relate to it. Further I would include the offset-value itself and all needed "translation" information - if beside the fiscal month the offset-month should be displayed or the years are shown with 2021/2022 or any similar stuff.

Like above hinted it will be important for many information that the offset-value is properly used within the date-functions respectively that these values are created without such functions - with interrecord-functions and/or mappings or similar functions.

The aim in the end should be not to query date-functions against today() or selected values within the set analysis else using the created flags like:

sum({< fiscalYTD = {1}>} value)

Further helpful may be to implement also a Canonical Date - Qlik Community - 1463578 to connect the calendars on another way as the dates-offset-key and/or to apply The As-Of Table - Qlik Community - 1466130 to combine the various flag-information.

- Marcus

[johngouws]

It is very rarely I come across someone using this calendar. When I do, and there is this type of comparison needed, I make it clear, in this object, the (mtd) calculation is based on a Calendar month. Although there are manufacturing targets, this object is nice to show how to are doing against the same month last year, mtd, as well as the months in between. It seems for now I will follow the same rules. 

By the way, this is the object I am referring to. 

Thanks a lot for the pointers,