1 Reply Latest reply: Mar 19, 2013 9:59 AM by McVeigh Stephen RSS

    Creating a calculated Bucket for stacked bar chart

      Hi

      I am  a bit of a newby to Qlikview and think I have biten off more than I can chew.

       

      I have a table

      DepartmentStudentEnrolledCourseretained
      Dept1STU11Course 11
      Dept2STU21Course 20
      Dept1STU31Course 10
      Dept1STU41Course 31
      Dept3STU51Course 41

      etc, etc

       

      where 1 = retained and 0 - not retained

       

      what I want to do is create create buckets of the % retained for each course ie =Sum (retained)/Sum (Enrolled)

       

      so I had say, 1 bucket for courses retaining over 50%, over 75% and 0ver 90%

       

      Everything I have tried so far has failed spectacularly, so won't embarrass myself by showing my workings.  I have looked through some previous discussions and can see how to create a bucet in the load script but cant figure out how to populate it with calculations

       

       

      any help anyone could provide would be hugely appreciated

       

      thanks

        • Re: Creating a calculated Bucket for stacked bar chart

          For info

          So far I have created the following table

           

          RetentionGp:

          load * INLINE

          [RetNR, RetainedGP

          1, <60

          2, 60-69

          3, 70-79

          4, 80-89

          5, 90+

          ];

           

          then loaded the main data table

           

          then created the following resident load

           

          retainedPercents:

          LOAD

              Course,

              Sum (retained)/Sum ([Enrolled]) AS PecentRetained

          RESIDENT BaseData

          Group by [Course];

           

          now I want to put my courses into buckets based on % retained using

           

          retainedBucket:

          load  [Course code],

              PecentRetained,

              if(PecentRetained<=59,1,

              (if(PecentRetained<=69,2,

              (if(PecentRetained<=79,3,

              (if(PecentRetained<=89,4,5)))))))as RetNR

          From retainedPercents

           

          this is where I am coming unstuck (system error -

          The system cannot find the file specified.)

          which is understandable but i can't think how I can reference a 'resident load' table

           

          I also feel i am making this this a bit more long winded than need be

           

          again, any help would be most appreciated (and maybe in about 10 years I can start to return the favour)