Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
rdspiess
New Contributor

Fiscal Calendar issues with FiscalYear/FiscalMonth/FiscalQuarter in Epressions

I used the following Fiscal Calendar Script I found on the Qlik Site...It works well Except I can not Load 'FiscalYear' or "FiscalMonth"  oe "FiscalQuarter \" from an expression.

Expression Examples:

Sum ({$<FiscalYear={"=Max(FiscalYear)"}>}[Sales Amount])

Sum ({$<FiscalYear={"=FiscalYear(Today())"}>}[Sales Amount]) Sum ({$<FiscalYear={vMaxFiscalYear}>}[Sales Amount])

Sum ({<FiscalYear={"=(Year(Today()) & '-' & (Year(Today())"}>}[Sales Amount])

Sum ({$<Year={"=Year(Today())"}>}[Sales Amount])

This is the only Expression that Works:

Sum ({$<FiscalYear={'2009-2010'}>}[Sales Amount])

Fiscal Calendar Script

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

LOAD

Date($(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1) AS Date,

RangeSum(Peek('RowNum'), 1) AS RowNum

AutoGenerate vEndDate - vStartDate + 1;

5 Replies

Re: Fiscal Calendar issues with FiscalYear/FiscalMonth/FiscalQuarter in Epressions

How about this?

=Sum({$<FiscalYear={"$(=MaxString(FiscalYear))"}>}[Sales Amount])

Re: Fiscal Calendar issues with FiscalYear/FiscalMonth/FiscalQuarter in Epressions

Or this?

=Sum ({$<FiscalYear={"=YearName(Max(FiscalYear), 0, $(vFiscalYearStartMonth))"}>}[Sales Amount])

Max() always returns a numerical value, which probably isn't really well suited to be assigned to the FiscalYear field. That's also why Sunny suggests to use the MaxString() function.

Peter

[Edit] Wasn't talking in set analysis terms. Now I am.

rdspiess
New Contributor

Re: Fiscal Calendar issues with FiscalYear/FiscalMonth/FiscalQuarter in Epressions

Thank You but returns 0

rdspiess
New Contributor

Re: Fiscal Calendar issues with FiscalYear/FiscalMonth/FiscalQuarter in Epressions

Thank You but Returns 0

Re: Fiscal Calendar issues with FiscalYear/FiscalMonth/FiscalQuarter in Epressions

Is there a sample you can share to show the issue?

Community Browser