5 Replies Latest reply: May 3, 2016 1:27 PM by Sunny Talwar RSS

    Fiscal Calendar issues with FiscalYear/FiscalMonth/FiscalQuarter in Epressions

    richard spiess

      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;