How to change the way AGGR handles Null values

[#1]

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.

[Anonymous]

use rangemax(max(aggr(sum(forecastrequirement),forecastversion)),0) instead

hope it wil work out

[#3]

Unfortunately this has not made any difference.

[#4 · Accepted solution]

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

[inoruoya]

thanks for your suggestion.

[Greg_Williams]

Awesome stuff. *  - didn't know about that little trick Thanks!