2 Replies Latest reply: Jun 10, 2013 10:22 AM by Raj Vadde RSS

    Count values

    Mario Van De Walle

      Hello,

       

      I have a listbox (only to show data) that contains a field "contracts_in_year1" that looks like

       

      Year1

      Value, frequency

      0     506

      1     12440

       

      Year2

      Value, frequency

      0     1206

      1     10577

       

      So to be clear there are 506 contracts that does not match and 12440 that are matching.

       

      Now I need to create a table

       

      Year1|count of only the matching contracts (12440)

      Year2|count of only the matching contracts (10577)

      etc...

       

      I tried the following to get only the valid contracts but it does not work:

      if (Year=1, count({<[Contracts Year1]={1}>}[Contracts Year1]),if(Year=2,count({<[Contracts Year2]={2}>}[Contracts Year2], etc...))

       

      Any Idea why this is not working?

       

      Thx

        • Re: Count values
          Stefan Wühl

          What do you mean with 'it does not work'? What do you get as result? And in what context are you using your expression?

           

          Besides this, I think you need to use 1 as value for every field modifier:

           

          if (Year=1, count({<[Contracts Year1]={1}>}[Contracts Year1]),if(Year=2,count({<[Contracts Year2]={1}>}[Contracts Year2], etc...))

           

          or to simplify your expression

           

          pick(Year

          ,count({<[Contracts Year1]={1}>}[Contracts Year1])

          ,count({<[Contracts Year2]={1}>}[Contracts Year2])

          )

          • Re: Count values
            Raj Vadde

            for denominator 

             

            count ({<Year 1= {1},contract year1 = {1}>}Contractyear1)

             

            count ({<Year2 = {1},contract year2 = {1}>}Contractyear2)