Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
milindnikumbh
New Contributor III

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

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.

4 Replies

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

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

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

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

Highlighted
milindnikumbh
New Contributor III

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

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.

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.