7 Replies Latest reply: Feb 27, 2013 8:39 AM by Henric Cronström RSS

    Avg and Stdev to consider nulls values

    Evgeny Denisov

      Hi, guys!

      How to get AVG function consider null values inside AGGR funtion?

       

      For example we selected three dates. What we need is:

      1) Aggregate Item Sales by Dates. So we have 3 values for each Item. For that purpose I use "AGGR(Sum(Sales),Date,Item)"

      2) Calculate AVG from this 3 values.


      Problem: If in Source Data there is no row with Sales for that Date, AGGR(Sum(Sales),Date,Item) returns null, instead of Zero.

      AVG function make calculation only among values, whitch are not Nulls. So average becomes wrong.

       

      How to solve that problem? It is not so impotant for calculating Average, because there is another way to calculate it, but I need it to behave that way

      for STDEV function.

      If it is impossible, I dont understand how to make XYZ-analisys work in Qlikview at all.

       

      We thought about unnormalization source data or something, but we think this not the better way.

       

      Qlikview question.png

       

      Source data looks like this

       

      Qlikview-Data structure.png