2 Replies Latest reply: Oct 6, 2012 3:23 AM by AK C RSS

    How to hide the value of certain dimensions even though need the hidden values in the chart for proper calculation of expression

      Hi Community,


      I have a document attached with a page called AERO.  On the page there is a line graph and table showing the same information.  I would only like to show the graph and the table with the values above a certain date say Feb-2013. 


      The issue is that the expression is a rangesum(...  expression and works a lot like accumulation.  If I hide the previous months, then it throws off the expression.  Any ideas?  The document is attached.

        • Re: How to hide the value of certain dimensions even though need the hidden values in the chart for proper calculation of expression
          John Witherspoon

          I don't tend to use rangesum() in expressions or the accumulation radio buttons on the expressions tab.  Instead, I tend to create an AsOf table.  If you're reporting by month, this table would have AsOfMonths in it.  Each AsOfMonth is connected in this table to every Month you want to include in the accumulation.  So let's say I'm doing a rolling twelve month average.  My table would contain some rows like this:


          AsOfMonth  Month
          Oct-2012   Oct-2012
          Oct-2012   Sep-2012
          Oct-2012   Aug-2012
          Oct-2012   Nov-2011
          Sep-2012   Sep-2012
          Sep-2012   Aug-2012
          Sep-2012   Oct-2011


          Now you use AsOfMonth as your chart dimension.  So let's say you can see Oct-2012 in the chart.  That actually maps to all 12 Months that you want to include in your rolling average.  So now you could do something like sum(Sales)/12 as your expression.  The difference between this and other kinds of accumulation is that if you select Oct-2012 in your chart, it will display that month, but it will still report correctly on the rolling twelve months up to that date.  You are, as the field name suggests, looking at the rolling 12 month average "as of" Oct-2012.


          Now, your data model and chart expressions were a bit more complicated than I'd like to tease apart to figure out how to apply this idea.  But this would tend to be my first choice for solving problems like this, and I'd turn to other solutions if I could see that this one wasn't going to do the job in a particular case.  One downside, for instance, is user confusion regarding what dates they're supposed to be selecting, since you now have two different month fields.


          This sort of table is an example of a more general philosophy of mine - if a problem can be solved in the data model, it should be solved in the data model.  I don't mean ever doing any accumulations in the data model, but merely setting up the data model so that QlikView will do everything for you with simple expressions, rather than using massive expressions built out of set analysis or other complicated techniques.  It doesn't always work, of course, but the data model is usually my first line of attack.


          Search the forum for "AsOf" with no spaces and you should find a lot of example files.