4 Replies Latest reply: Feb 20, 2013 9:09 AM by danielle_v RSS

Null Values in Expressions

danielle_v

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!

  • Re: Null Values in Expressions
    Jonathan Brough

    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

  • Re: Null Values in Expressions
    danielle_v

    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!

    • Re: Null Values in Expressions
      Jonathan Brough

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

       

      ForecastCal_Date = {$(vDateSelection)+7}

       

      Jonathan

      • Re: Null Values in Expressions
        danielle_v

        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.