Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Since writing this question I've made a little progress but still require some help please. I have the max(aggr(sum(forecastrequirement),forecastversion) working correctly. However, the Min(...) version returns NULL for months containing a week without a forecast. I believe this is because Qlikview creates a forecast record for such weeks with the forecastversion as NULL. As this "NULL" forecast version has no forecastrequirement it is returned as the minimum value. I have tried to use a set statement to exclude NULL forecastversions to no effect. Any advice welcome.
Help! I'm loading forecast data which is stored by week. I use a different table to aggregate from week to month to year etc. There are some weeks with no forecast. If I use sum(forecastrequirement) in a table that uses month, the table shows the total of the forecast values within the month.
Within the data I have several forecasts - each identified by a "forecastversion" field. I want to compare the forecasts by month, showing the min, max and most recent forecast values for the month. I used max(aggr(sum(forecastrequirement), forecastversion)) to calc the maximum forecast for the month - which is where I encountered a problem.
Max(aggr(sum(forecastrequirement),forecastversion)) returns NULL. I think this may be because there are weeks where the forecast does not exist, so Qlikview creates a record with a NULL value for the forecast requirement.
.
I've put the aggr(sum(forecastrequirement),forecastversion) in a table, with forecast version and month as the dimensions. The Aggr(..) returns NULL whilst the sum(forecastversion) returns the correct result. Interestingly, the table also included the max(aggr(sum(forecastrequirement),forecastversion)) formula. This returns NULL when more than one month is visible. If I select a single month the correct value is returned for the max(...) formula but NULL is returned for the sum(...) formula.
I'm very confused! Thanks for any help.
Once I understood the problem I found a solution on the site. The syntax is as follows to exclude the NULL forecast versions.
MIN(aggr(Sum({$<ForecastVersion = {'*'}>} ForecastRequirement),ForecastVersion,ContractMonth))
use rangemax(max(aggr(sum(forecastrequirement),forecastversion)),0) instead
hope it wil work out
Unfortunately this has not made any difference.
Once I understood the problem I found a solution on the site. The syntax is as follows to exclude the NULL forecast versions.
MIN(aggr(Sum({$<ForecastVersion = {'*'}>} ForecastRequirement),ForecastVersion,ContractMonth))
thanks for your suggestion.
Awesome stuff. * - didn't know about that little trick Thanks!