7 Replies Latest reply: Apr 5, 2016 8:04 AM by Linda Clark RSS

    Projections by Fiscal Year

    Linda Clark

      In the attached sample qvw, I am trying to project estimated hours for Hearings by Judges.    We are combining data from several different docket types, but this sample only includes two of these (Central/Family Dockets and DFPS Docket).

       

      The top chart shows this projection done by Year/Month modeled after some examples I found out in the Qlik Community.  The calculated year/month projections exactly align with the linear trend line.

       

      However, when I try to then do this by fiscal year, using a fiscal year continuous axis instead, the forecast is really, really off from the fiscal year linear trend line.

       

      How can I do projections by Fiscal Year?  Any help would be greatly appreciated!

        • Re: Projections by Fiscal Year
          Stefan Wühl

          Linda,

           

          I believe you need to restrict the CompareFiscalYear to years below the vCurrFY in the linest functions, to avoid getting a partial year into the calculcations, which will distort the slope calculation as observed:

           

          CompareFiscalYear = {"=Right(CompareFiscalYear,4)<Right('$(vCurrFY)',4)

           

          [the set modifier can be simplified after adding a numeric field in the script]

           

          edit: reattached file

          • Re: Projections by Fiscal Year
            Linda Clark

            On the bottom chart by Fiscal Year in qvw#3 I attached above, I modified how the forecast was calculated as follows, based on your suggestion (at least I think I did).  However, the calculated projection values are no where near what the Qlikview provided linear regression line shows.

             

            Our hearing activity is going up every year in reality -- but my calculated forecast values don't even bring us back to our current activity level until 2025, so how can that be right?

             

            The following is how I modified my forecast expression based on your initial input:

             

            =if( Right(CompareFiscalYear,4) >= Right('$(vCurrFY)',4),

            linest_b({< CompareType = {'Actuals'}, CompareSettingResultCategory = {'Heard / Order / Held'}, CompareDocketCategory = {'Regular District Docket'}, CompareDivision = {'District'} >}
            total aggr(nodistinct if(sum({< CompareType = {'Actuals'}, CompareSettingResultCategory = {'Heard / Order / Held'}, CompareDocketCategory = {'Regular District Docket'}, CompareDivision = {'District'} >} CompareEstHours),
            sum({< CompareType = {'Actuals'}, CompareSettingResultCategory = {'Heard / Order / Held'}, CompareDocketCategory = {'Regular District Docket'}, CompareDivision = {'District'} >} CompareEstHours)),
            FYContinuousAxis),FYContinuousAxis)
            +
            linest_m({< CompareType = {'Actuals'}, CompareSettingResultCategory = {'Heard / Order / Held'}, CompareDocketCategory = {'Regular District Docket'}, CompareDivision = {'District'} >}
            total aggr(nodistinct if(sum({< CompareType = {'Actuals'}, CompareSettingResultCategory = {'Heard / Order / Held'}, CompareDocketCategory = {'Regular District Docket'}, CompareDivision = {'District'} >} CompareEstHours),
            sum({< CompareType = {'Actuals'}, CompareSettingResultCategory = {'Heard / Order / Held'}, CompareDocketCategory = {'Regular District Docket'}, CompareDivision = {'District'} >} CompareEstHours)),
            FYContinuousAxis),FYContinuousAxis) * only({1} FYContinuousAxis)
            ,
            if( Right(CompareFiscalYear,4) >=  Right('$(vFYMinYear)',4) and Right(CompareFiscalYear,4) < Right('$(vCurrFY)',4),
            sum({< CompareType = {'Actuals'}, CompareSettingResultCategory = {'Heard / Order / Held'}, CompareDocketCategory = {'Regular District Docket'}, CompareDivision = {'District'} >} CompareEstHours))
            )

              • Re: Projections by Fiscal Year
                Stefan Wühl

                Well, that's not what I've suggested, I just modified the set analysis in the linest_m and linest_b functions.

                 

                When I open your recent sample file and look at the third line chart at the bottom, I see this:

                 

                2016-04-04 23_08_11-QlikView x64 - [C__Users_Stefan_Downloads_Projections Stripped_3.qvw_].png

                 

                Isn't that what you are expecting?

                 

                This is the expression for Central/Family Forecast:

                 

                =if( CompareFiscalYear >= '$(vCurrFY)',

                 

                linest_b({< CompareType = {'Actuals'}, CompareSettingResultCategory = {'Heard / Order / Held'}, CompareDocketCategory = {'Regular District Docket'}, CompareDivision = {'District'}, CompareFiscalYear = {"=Right(CompareFiscalYear,4)<Right('$(vCurrFY)',4)"} >}

                  total aggr(nodistinct if(sum({< CompareType = {'Actuals'}, CompareSettingResultCategory = {'Heard / Order / Held'}, CompareDocketCategory = {'Regular District Docket'}, CompareDivision = {'District'},CompareFiscalYear = {"=Right(CompareFiscalYear,4)<Right('$(vCurrFY)',4)"}>} CompareEstHours),

                  sum({< CompareType = {'Actuals'}, CompareSettingResultCategory = {'Heard / Order / Held'}, CompareDocketCategory = {'Regular District Docket'}, CompareDivision = {'District'},CompareFiscalYear = {"<=$(vCurrFY)"} >} CompareEstHours)),

                  FYContinuousAxis),FYContinuousAxis)

                +

                linest_m({< CompareType = {'Actuals'}, CompareSettingResultCategory = {'Heard / Order / Held'}, CompareDocketCategory = {'Regular District Docket'}, CompareDivision = {'District'} >}

                  total aggr(nodistinct if(sum({< CompareType = {'Actuals'}, CompareSettingResultCategory = {'Heard / Order / Held'}, CompareDocketCategory = {'Regular District Docket'}, CompareDivision = {'District'},CompareFiscalYear = {"=Right(CompareFiscalYear,4)<Right('$(vCurrFY)',4)"} >} CompareEstHours),

                  sum({< CompareType = {'Actuals'}, CompareSettingResultCategory = {'Heard / Order / Held'}, CompareDocketCategory = {'Regular District Docket'}, CompareDivision = {'District'},CompareFiscalYear = {"=Right(CompareFiscalYear,4)<Right('$(vCurrFY)',4)"} >} CompareEstHours)), FYContinuousAxis),FYContinuousAxis)

                * only({1} FYContinuousAxis)

                 

                ,sum({< CompareType = {'Actuals'}, CompareSettingResultCategory = {'Heard / Order / Held'}, CompareDocketCategory = {'Regular District Docket'}, CompareDivision = {'District'} >} CompareEstHours))

                 

                 

                Could you check that you can reproduce that the results are correct?

                  • Re: Projections by Fiscal Year
                    Stefan Wühl

                    Maybe this explains better what the issue is:

                     

                    The aggr() in the linest_m function is building a virtual table, the slope is calculated from the table values:

                     

                    CompareFiscalYear FYContinuousAxis Central/Family Est Hours
                    101342,25
                    FY20091911865,38
                    FY20102012575,45
                    FY20112111905,65
                    FY20122213593,38
                    FY20132313647,28
                    FY20142415131,70
                    FY20152515050,67
                    FY2016267572,73

                     

                    The problem is the last line which shows only data for part of the FY. The low value distorts the slope calculation.

                     

                    If I add the set modifier for CompareFiscalYear:

                    CompareFiscalYear FYContinuousAxis Central/Family Est Hours
                    93769,52
                    FY20091911865,38
                    FY20102012575,45
                    FY20112111905,65
                    FY20122213593,38
                    FY20132313647,28
                    FY20142415131,70
                    FY20152515050,67

                     

                    The last line is gone.

                     

                    There might be better way to tackle this issue than what I suggested in my sample QVW, I just wanted to highlight the cause of your issue.

                • Re: Projections by Fiscal Year
                  Linda Clark

                  Stefan,  Thank you so much!  That is exactly what I needed.   Now the calculated projections are in line with the trend line.