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

Fiscal Calendar issues with FiscalYear/FiscalMonth/FiscalQuarter in Epressions

rdspiess — Tue, 03 May 2016 14:17:07 GMT

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;

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

sunny_talwar — Tue, 03 May 2016 15:14:24 GMT

How about this?

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

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

Peter_Cammaert — Tue, 03 May 2016 15:50:01 GMT

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.

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

rdspiess — Tue, 03 May 2016 17:20:30 GMT

Thank You but returns 0

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

rdspiess — Tue, 03 May 2016 17:21:16 GMT

Thank You but Returns 0

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

sunny_talwar — Tue, 03 May 2016 17:27:02 GMT

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