Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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'} >}
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?
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
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
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
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))
)
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'} >}
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?
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.
Stefan, Thank you so much! That is exactly what I needed. Now the calculated projections are in line with the trend line.