topic Re: 2 Calendars in a single dashboard ? Query in QlikView

2 Calendars in a single dashboard ? Query

snehamahaveer — Mon, 27 Jul 2015 10:19:54 GMT

Hi ,

I am currently in the process of updating a tab in an exisiting dashboard. My issue with the process is that the current dashboard uses Standard calendar for all its tabs and the one I am updating requires the Fiscal calendar. How could I use both calendars in a dashboard. ? As this is my first dashboard, I am stuck with the basics. Any help would be useful.

Thanks.

Re: 2 Calendars in a single dashboard ? Query

jonathandienst — Mon, 27 Jul 2015 11:23:24 GMT

You still only need a single master calendar . The calendar would use date as the key and include calendar month and calendar year as well as fiscal month/period and fiscal year in the same table.

Re: 2 Calendars in a single dashboard ? Query

jonathandienst — Mon, 27 Jul 2015 11:36:36 GMT

Here is a simple script:

Let vFiscalStart = 4; //first month of fiscal year

Let vMinDate = Num(MonthStart(Today(), -24)); //or other logic if required

Let vMaxDate = Floor(MonthEnd (Today()));

Calendar:

LOAD *,

If(Month < $(vFiscalStart), Year - 1, Year) As FiscalYear,

If(Month < $(vFiscalStart), Month + 13 - $(vFiscalStart), Month + 1 - $(vFiscalStart)) As FiscalPeriod

;

LOAD Date(nDate) As Date,

Year(nDate) As Year,

Month(nDate) As Month,

Day(nDate) As Day

// other date fields as required...

;

LOAD $(vMinDate) + RecNo() - 1 As nDate

AUTOGENERATE $(vMaxDate) - $(vMinDate) + 1;

(Edit: changed RowNo() to RecNo())

Re: 2 Calendars in a single dashboard ? Query

maximiliano_vel — Tue, 04 Aug 2015 22:01:20 GMT

Try this from Rob Wunderlich

Search Recipes | Qlikview Cookbook

Regards

Re: 2 Calendars in a single dashboard ? Query

snehamahaveer — Wed, 05 Aug 2015 08:41:38 GMT

Thank you Jonathan Dienst and Maximiliano Velez. It so happens that the master calendar has provisions for fiscal year and the std year format, all I had to do was create the values and set it to FIN_month to denote financial month.

I did try using the script given and it made the system slow. I am not sure of the reason behind it.

jonathan dienst