1 Reply Latest reply: Mar 22, 2012 1:05 AM by jamesf65 RSS

    Confused on trellises and cumulative sums

      I've got a chart that I'm happy with, that displays the cumulative percentage of a variable across the dimension:

       

      cumulative.png

      Variable is count(ID)/count(total <Dimension1> ID) with Accumulation set to full accumulation.

       

      My problem comes if I try to build a trellis on Dimension2: I get this:

       

      trellis.png

      I'm rather unclear what I should do to make it behave nicely within the trellis - the cumulative increases don't seem to be working any more (look at eg B2, where the distribution looks normal when it should be strictly increasing)

        • Confused on trellises and cumulative sums

          Solved - took a bit of searching on here, but a combination of http://community.qlik.com/message/89017#89017 and http://community.qlik.com/thread/31671 have sorted it out;

           

          Variable becomes

           

          sum(Measure)/sum(Total <Dimension2> Measure)

           

          Instead of using Dimension1, I used an AsOf table, as John Witherspoon http://community.qlik.com/people/johnw suggests - that meant I didn't need to use accumulation in the chart.

           

          The only odd thing is that on my client (QV11) John's script for generating an AsOf table didn't work.  I've duplicated the script from here http://community.qlik.com/message/89017#89017 and it only works if I add a second dimension with the same values into the load.  Any ideas why?

           

          Dimensions:

          LOAD Dimension

          FROM

          [C:\Users\jforeman\Downloads\Temp\2012-03\Dimensions.xlsx]

          (ooxml, embedded labels, table is Sheet1);

            

           

          AsOf:

          LOAD DISTINCT Dimension, Dimension AS Dimension1

          RESIDENT Dimensions

          ;

             

          LEFT JOIN (AsOf)

          LOAD Dimension1 as AsOfDimension1

          RESIDENT AsOf

          ;

          INNER JOIN (AsOf)

          LOAD *

          ,if(AsOfDimension1=Dimension1,'Current','AllPrevious') as Dimension1Type

          RESIDENT AsOf

          WHERE AsOfDimension1>=Dimension1

          ;