Count values

[Anonymous]

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

[swuehl · Accepted solution]

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])

)

[Anonymous]

for denominator 

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

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