Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
rdspiess
Contributor
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
sunny_talwar

How about this?

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

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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
Contributor
Contributor
Author

Thank You but returns 0

rdspiess
Contributor
Contributor
Author

Thank You but Returns 0

sunny_talwar

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