2 Replies Latest reply: Jun 13, 2017 7:26 AM by Jim Morrison RSS

    Straight table dimension limit for a range of accumulated percentages

    Jim Morrison

      Hi Guys,

       

      Before we get started I believe what I'm asking may not be possible in Qlikview, but thought it would be worth posting before finally accepting that is the case.

       

      A bit of context on what I'm after...

       

      I've been tasked with creating a dashboard which shows the largest stock value/volume amount in descending order, with a culmilative percentage sum to allow our users to see cut of points such as 10%,30% of total etc.

       

      Unfortunately this data set needs to be dynamic so I am having to use Qlikview's inbuilt chart accumulation features rather than going down the RangeSum/Above syntax route.

       

      What we're left with is a straight table with 1 dimension, [Item No], which looks something like this:

       

       

      With the following expression being used to calculate the percentile field:

       

      Bearing in mind I am using the 'Full Accumulation' selection on the [Percentile] field to get this to calculate correctly.

       

      I then have a slider on the page which is setting the value for a variable 'vPercentile' to limit the data relative to the accumulated total:

       

      This is all fine and is working as expected, this is where the problem arises...

       

      I have now been asked if it would be possible to limit the data between specific %s, for example 60-80% of the total.

       

      I know this is possible as I have used a multislider before, however this has always been with an actual date field rather than a variable.

       

      For this specific problem I am struggling to see how it would be possible, as my field [Percentile] is not viewed as say 61% outside of the straight table where the accumulation is taking place.

       

      Is it possible for me to set a range using variables within the dimension limits tab, for example something along the lines of:

       

      vMinPercentile = 60

      vMaxPercentile = 80

       

      > vMinPercentile , < vMaxPercentile

       

      I cant see how this would work as there is no field to actually base these filters against as I cannot reference the expression within the dimension limit expression box.

       

      Any help with this would be appreciated or just the knowledge that what I have said is correct and with these specific criteria this is impossible.

       

      Cheers

      Jim