topic Re: Projections by Fiscal Year in QlikView

Projections by Fiscal Year

Mon, 04 Apr 2016 15:21:19 GMT

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

swuehl — Mon, 04 Apr 2016 17:02:55 GMT

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

Mon, 04 Apr 2016 18:54:43 GMT

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

Re: Projections by Fiscal Year

swuehl — Mon, 04 Apr 2016 20:17:14 GMT

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

Re: Projections by Fiscal Year

Mon, 04 Apr 2016 20:33:47 GMT

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

swuehl — Mon, 04 Apr 2016 21:13:49 GMT

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:

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'} >}

* 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

swuehl — Mon, 04 Apr 2016 22:01:59 GMT

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
FY2009	19	11865,38
FY2010	20	12575,45
FY2011	21	11905,65
FY2012	22	13593,38
FY2013	23	13647,28
FY2014	24	15131,70
FY2015	25	15050,67
FY2016	26	7572,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
FY2009	19	11865,38
FY2010	20	12575,45
FY2011	21	11905,65
FY2012	22	13593,38
FY2013	23	13647,28
FY2014	24	15131,70
FY2015	25	15050,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

Tue, 05 Apr 2016 12:04:02 GMT

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