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: 
Not applicable

Projections by Fiscal Year

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!

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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?

View solution in original post

7 Replies
swuehl
MVP
MVP

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

Not applicable
Author

swuehl,

Thanks for the quick reply.  However, I must be doing something wrong or not understanding what you are suggesting below.

Attached is an updated qvw trying to implement your suggestion (I think), but it is not making a difference.

Could you let me know what I am missing on this?

Thanks so much.  Linda

swuehl
MVP
MVP

Hi Linda,

hasn't the third line chart at the bottom showed the correct projection?

I haven't really understood what you updated in your new sample QVW, could you elaborate on your changes?

Thanks,

Stefan

Not applicable
Author

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))
)

swuehl
MVP
MVP

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?

swuehl
MVP
MVP

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.

Not applicable
Author

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