topic Re: FY into Existing Master Calendar in QlikView

FY into Existing Master Calendar

Wed, 09 Sep 2015 09:28:34 GMT

Hi All,

I am trying to add Financial Years into my existing Master Calendar and having done some browsing and reading it appears there are a few methods but I could not see one with the right answer

I want to maintain the calendar below but also want to add in FY as an extra option is this a possibility?

Toby

/////////////////////////////
// QLIKVIEW MASTER CALENDAR//
/////////////////////////////

QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);

Temp:
Load
              min(CMDBDate) as minDate,     // <<<<< Change Name of Date Field Here
              max(CMDBDate) as maxDate       // <<<<< Change Name of Date Field Here
Resident ReportView;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;

TempCalendar:
LOAD
               $(varMinDate) + Iterno()-1 As Num,
               Date($(varMinDate) + IterNo() - 1) as TempDate
               AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

MasterCalendar:
Load
    TempDate AS CMDBDate, // <<<<< Change Name of Date Field Here
    week(TempDate) As Week,
    Year(TempDate) As Year,
    Month(TempDate) As Month,
    Day(TempDate) As Day,
    WeekEnd(TempDate) as WeekEndDate,
    WeekStart(TempDate) as WeekStartDate,
    YeartoDate(TempDate)*-1 as CurYTDFlag,
    YeartoDate(TempDate,-1)*-1 as LastYTDFlag,
    inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,
    date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,
    ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
    Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
    WeekDay(TempDate) as WeekDay

Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;

Re: FY into Existing Master Calendar

krishna20 — Wed, 09 Sep 2015 10:00:13 GMT

Hi,

This Link surely helps you

Fiscal and Standard Calendar generation

Regards

Krishna

Re: FY into Existing Master Calendar

sasiparupudi1 — Wed, 09 Sep 2015 10:14:38 GMT

use addmonths and addyears functions

example

addmonths(addyears(TempDate,1),-3) as FiscalDate

Month(addmonths(addyears(TempDate,1),-3)) as FiscalMonth,

Year(addmonths(addyears(TempDate,1),-3)) as FiscalYear

hth

Sasi

Re: FY into Existing Master Calendar

balasundaram — Wed, 09 Sep 2015 10:18:51 GMT

Hi,

Kindly find below script line

YearName(TempDate, 0, 4)) AS FiscalYear,

Re: FY into Existing Master Calendar

Wed, 09 Sep 2015 13:06:51 GMT

Thank you so much Sasidhar!

How would I create fiscal QTRs? As the Calendar QTRs are created using a map...

Re: FY into Existing Master Calendar

sasiparupudi1 — Wed, 09 Sep 2015 13:15:28 GMT

you can use

ApplyMap('QuartersMap', Month(addmonths(addyears(TempDate,1),-3)), Null()) as FiscQuarter

hth

Sasi

Re: FY into Existing Master Calendar

Wed, 09 Sep 2015 13:26:03 GMT

Thank you very very much Sasidhar, you have made my day!

Re: FY into Existing Master Calendar

sasiparupudi1 — Wed, 09 Sep 2015 13:31:05 GMT

You are Welcome. Glad that I could help

Sasi

Re: FY into Existing Master Calendar

Thu, 10 Sep 2015 13:50:03 GMT

Hi Sasidhar,

In regards to the awesome help yesterday, how do I format the Financial year in the script to come out like 2015/2016 etc?

Toby

Re: FY into Existing Master Calendar

sasiparupudi1 — Thu, 10 Sep 2015 14:01:07 GMT

try to add to your calendar

=Year(addmonths(addyears(TempDate,1),-3)) &'/' & Year(TempDate) as FiscalYearDisplay

hth

Sasi

Re: FY into Existing Master Calendar

Thu, 10 Sep 2015 14:24:44 GMT

Thank you very very much!

Re: FY into Existing Master Calendar

Thu, 10 Sep 2015 14:47:40 GMT

Sorry Sasidhar that did not work and is bringing back some weird combinations lol

Re: FY into Existing Master Calendar

sasiparupudi1 — Fri, 11 Sep 2015 06:09:15 GMT

hi please can you share a sample or what combinations is it bringing?

how are you selecting your dates?

Sasi

Re: FY into Existing Master Calendar

Mon, 14 Sep 2015 07:46:26 GMT

Hi Sasidhar,

It was bringing back combinations such as 2013/2013 and 2016/2016 not just the standard FY setup

Toby

Re: FY into Existing Master Calendar

sasiparupudi1 — Mon, 14 Sep 2015 07:57:28 GMT

Please, could you give me some example dates where your are getting 2013/2013 or 2016/2016 combination?

thx

Sasi

Re: FY into Existing Master Calendar

sasiparupudi1 — Mon, 14 Sep 2015 08:04:02 GMT

I think I know the problem.. you need a if condition here

please try

=if(Year(addmonths(addyears(TempDate,1),-3)) =Year(TempDate),year(addyears(TempDate,1)) &'/'& year(TempDate),Year(addmonths(addyears(TempDate,1),-3)) &'/' & Year(TempDate))

hth

Sasi

Re: FY into Existing Master Calendar

Fri, 02 Oct 2015 09:40:52 GMT

Apologies for the late response.

Where would I put this statement within the Master Calendar?

Re: FY into Existing Master Calendar

sasiparupudi1 — Fri, 02 Oct 2015 10:07:28 GMT

replace

Year(addmonths(addyears(TempDate,1),-3)) &'/' & Year(TempDate) as FiscalYearDisplay

with

if(Year(addmonths(addyears(TempDate,1),-3)) =Year(TempDate),year(addyears(TempDate,1)) &'/'& year(TempDate),Year(addmonths(addyears(TempDate,1),-3)) &'/' & Year(TempDate)) as FiscalYearDisplay

hth

Sasi

Re: FY into Existing Master Calendar

Fri, 02 Oct 2015 10:17:00 GMT

Thank you again Sasidhar!!!

How can I show it the other way round? as it shows 2015/2016 as 2016/2015?

Toby

Re: FY into Existing Master Calendar

sasiparupudi1 — Fri, 02 Oct 2015 11:01:04 GMT

=if(Year(addmonths(addyears(TempDate,1),-3)) =Year(TempDate),year(TempDate) &'/'& year(addyears(TempDate,1)), Year(TempDate)&'/' & Year(addmonths(addyears(TempDate,1),-3)))