Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
danielle_v
Contributor

Null Values in Expressions

Hi All,

I'm currently trying to put together a straight table to show forecasted volumes, but I'm having problems with null values. My chart has one dimension - country. I then have multiple expressions calculating forecasted volumes from each of these countries. Expression 1 is as follows, and calculates the ForecastCBM for the selected week/year;

ceil((sum({$<ForecastCal_Week = {$vWeekSelection)},
ForecastCal_Year = {$(vYearSelection)}>} ForecastCBM / vStep) * Adjustment

The second expression is as follows, and calculates the ForecastCBM for the following week (i.e. if the first expression is showing week 11, the second will show week 12);

sum({<ForecastCal_Year = , ForecastCal_Week = , WeekDateNum =
{$(=Num(WeekStart(Min(WeekDateNum), +1))))}>}ForecastCBM ) / vStep * Adjustment


The issue is that if the value returned by expression 1 is null (i.e. no value for forecastCBM in that week / country), then the second calculation returns a null in the chart even though there is a value for ForecastCBM in week+1 (this can be proved by selecting the week+1 value in the calendar). If I set the chart to show null values, the chart shows nulls for all expressions, even when a value should be returned.

Any help would be greatly appreciated, as I can't seem to get anything to work!

4 Replies
jonbrough
Valued Contributor

Re: Null Values in Expressions

Can't you isolate the date selection element of the expression in the same way you did for the first week. Something like: ForecastCal_Week = {$(vWeekSelection)+1}

Jonathan

danielle_v
Contributor

Re: Null Values in Expressions

Hi Both,

Many thanks for your quick responses, this is much appreciated.

Unfortunately though, I still can't seem to get the expressions to work as desired.

The original version of my chart was similar to the suggestions you have made above, and this seemed to work ok apart from where the date parameters spanned the start of a new year (i.e. if the date selections made were 2012 week 52, I would want expression 2 to show 2013 week 1). We then got a condultant in who made the changes to the second expression and it seems to work ok except from the issue with the nulls.

This may well be a really easy problem to solve, but unfortunately my qlikview knowledge isn't great and I'm struggling!

Thanks again for your help so far!

jonbrough
Valued Contributor

Re: Null Values in Expressions

Refer to dates instead of week numbers then. These won't be impacted by the turn of the year.

ForecastCal_Date = {$(vDateSelection)+7}

Jonathan

danielle_v
Contributor

Re: Null Values in Expressions

Many thanks for your help so far all!

However, I still can't seem to get the results I'm looking for. Whichever way I calculate the week numbers, I still can't get a result for the 2nd, 3rd, 4th expressions for particular dimensions if the result for that dimension on the first expression is null.

For example, if I select ForecastCal_Week 10 and there is no forecasted value for France in expression 1, expression 2 (which shows week 11) will also be blank for France. However, if i select ForecastCal_Week 11 on the calendar, expression 1 will correctly show the forecasted value for France.

The countries that have a value against them in the first expression are all correctly calculating the values in the subsequent expressions, it's just the countries that are null in the first expressions that are not doing as they should in the following expressions.