8 Replies Latest reply: Feb 14, 2013 5:41 AM by Bruno Mølgaard Geertsen RSS

    Counting sum in variable

      Hi all

      I'm working on a dataset with student applications to a University where we have 66 different types of educations (Biology, Law, Economics etc.).


      For that I need to make a Straight Table (preferably) where I can calculate the following:


      A Count of Number of Educations this Year that have: '0' applications, '1-10' applications, '11-30' applications, '31-100' applications and '>100' applications.



      Number of applications.png

      I'm able to count the number of educations

      (Count(DISTINCT {1<Year={$(vCurrentYear)}>}  [Education])      )


      But not the number of Educations that have '0' applications, '1-10' applications and so forth.


      Can anyone help me on this please

      Thanks in advance




        • Re: Counting sum in variable
          Sunil Chauhan

          in script



          if(applicant fieldname=0,0,

          if(applicant fieldname>0 and applicant fieldname<=10,'1-10',

          if(applicant fieldname>10 and applicant fieldname<=30,'10-30',

          if(applicant fieldname>30 and applicant fieldname<=100,'10-30',

          if(applicant fieldname>100,'>100')))))  as youfieldname



          and take this youfieldname in dimension and drag it tohorizontally




          hope this helps

            • Re: Counting sum in variable

              Hi Luminary

              Thanks for your quick reply, I can't get it to work though.


              I've done the following in the script:



              if(Education>0 and Education<=10,'1-10',

              if(Education>10 and Education<=30,'11-30',

              if(Education>30 and Education<=100,'31-100',

              if(Education>100,'>100')))))  as EducationCountGrouped,


              Can you see what I'm doing wrong?




              I've attached a cleaned copy of my data in the original post so you can see it






                • Re: Counting sum in variable
                  Sunil Chauhan

                  its looking good to me

                  but dont know  why its not working


                  Please attached file stating requirement again without any gap if possible





                  hope this helps

                    • Re: Counting sum in variable

                      Hi Luminary

                      I'm not sure I expressed myself appropiate in my original post. I'm sorry, I'll try again:


                      The thing i'm looking for is the following:


                      I have a dataset with app. 10.000 rows, 1 for each person who applied to an Education on our University. We have 66 different Educations an applicant can choose from, f.ex. Biology, Law, Medicin and Economics. So if there is 29 applicants for Biology the dataset have 29 rows under the Education 'Biology' (1 for each person who applied).


                      I need to make a table that can summarize how many Educations that have had 0 applications, 1-10 applications, 11-30 applications 31-100 applications and >100 applications. F.ex. Biology recieved 29 applications, Economics 71 applications, Law 201 applications and Medicin 1.504 applications.


                      Based on these 4 Educations, my table would need to look like this:


                      0 app.   1-10 app.   11-30 app.   31-100 app.   >100 app.

                          0             0                 1                  1                     2


                      if this is also what you understood in the first place, what do I need to write in my expression in my table? I can't' figure it out


                      Thanks a lot


                • Re: Counting sum in variable
                  Gysbert Wassenaar

                  See attached qvw