Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
How about this?
=Sum({$<FiscalYear={"$(=MaxString(FiscalYear))"}>}[Sales Amount])
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.
Thank You but returns 0
Thank You but Returns 0
Is there a sample you can share to show the issue?