topic Re: Derive Fiscal Year / Month / Week from Input Box on the fly. in QlikView

Derive Fiscal Year / Month / Week from Input Box on the fly.

milindnikumbh — Fri, 10 Nov 2017 11:19:18 GMT

Dear All ,

I have given Input box to enter date to user. Can I derive Fiscal year / Month / Week of the entered date on dashboard.

I know in script, but not able to derive on the fly using variables on dashboard .

Example : In Input box , user entered 09/04/2010 , I want in 2010-2011 as a Fiscal Year , Apr as a month and 1 as week no.

Please suggest.

Regards,

Milind.

Re: Derive Fiscal Year / Month / Week from Input Box on the fly.

Anil_Babu_Samineni — Fri, 10 Nov 2017 13:06:29 GMT

May be explain how you done this in script, We may offer you on fly..

Re: Derive Fiscal Year / Month / Week from Input Box on the fly.

Colin-Albert — Fri, 10 Nov 2017 13:16:49 GMT

I would suggest you consider using a calendar table that includes the date and fields for WeekYear, Fiscal Year, Month, WeekNo etc, as although there is a standard weekno() function in Qlik it is based on iso week numbers which may not map directly to your usage.

Also week numbers can introduce issues like a short week 1, week 53, the year for week numbers may not match the calendar year in week1/53 etc. So week numbers are not necessarily simple.

The easiest solution may be to create a lookup table in Excel and import that rather than defining the week number in the script.

See these posts for more details.

Redefining the Week Numbers

Fiscal Year

Calendars

Re: Derive Fiscal Year / Month / Week from Input Box on the fly.

milindnikumbh — Fri, 10 Nov 2017 14:12:22 GMT

Hello ,

I am using following script to build fiscal year calendar. Its working perfectly. My client wants to add the input box to accept the date and derive the fiscal year / fiscal month from that input parameter.

/* Fiscal year */

SET vFiscalYearStartMonth = 4;
LET vStartDate = Num(YearStart(Today(), -1));
LET vEndDate = Num(YearEnd(Today()));

FiscalCalendar:
LOAD
*,
Dual('Q' & Ceil(FiscalMonth/3), Ceil(FiscalMonth/3)) AS FiscalQuarter, // Fiscal Calendar Quarter
Dual(Text(Date(MonthEnd(Date), 'MMM')), FiscalMonth) AS FiscalMonthName; // Fiscal Calendar Month Name
LOAD
*,
Year(Date) AS Year, // Standard Calendar Year
Month(Date) AS Month, // Standard Calendar Month
Date(MonthEnd(Date), 'MMM') AS MonthName, // Standard Calendar Month Name
Dual('Q' & Ceil(Month(Date)/3), Ceil(Month(Date)/3)) AS Quarter, // Standard Calendar Quarter
Mod(Month(Date) - $(vFiscalYearStartMonth), 12)+1 AS FiscalMonth, // Fiscal Calendar Month
YearName(Date, 0, $(vFiscalYearStartMonth)) AS FiscalYear, // Fiscal Calendar Year
Date(MonthEnd(Date), 'MMM')&'-'&Year(Date) AS MonthYear;
LOAD
Date($(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1) AS Date,
RangeSum(Peek('RowNum'), 1) AS RowNum
AutoGenerate vEndDate - vStartDate + 1;

Thanks.

Regards,

Milind.

Re: Derive Fiscal Year / Month / Week from Input Box on the fly.

Colin-Albert — Fri, 10 Nov 2017 14:28:37 GMT

This is just simple association within the Qlik data model.

Add a list box with Date, and othets with Fiscal Year,. FiscalMonth etc

When you select a Date, the correct Fiscal Year and Month should show. Does the year show as 2010-2011?

Then try a text box showing maxstring(Fiscal Year) and maxstring(FiscalMonth) which should show the correct values for the selected date.