2 Replies Latest reply: Jan 12, 2016 2:57 AM by Manoj Kulkarni

# how to if count per specific year

For example I have this data.

```cyear     pin     tax
2015     123     12,000.00
2015     123     12,000.00
2015     123     12,000.00
2016     123     12,000.00
2016     123     12,000.00
2016     123     12,000.00
2016     123     12,000.00
```

And this is my expression;

```If(\$(Tmonths)=7 or \$(Tmonths)=8 or \$(Tmonths)=9,
sum({<cyear= {\$(Tyear)}, qtr={"2"}>} aggr(DISTINCT Basic,qtr,cyear,OwnerNum,BINum,PCINum,SINum,MDINum,Parcel,PType))*(\$(Tmonths)*2/100 - .06) +
sum({<cyear= {\$(Tyear)}, qtr={"2"}>} aggr(DISTINCT SEF,qtr,cyear,OwnerNum,BINum,PCINum,SINum,MDINum,Parcel,PType))*(\$(Tmonths)*2/100 - .06),

If(\$(Tmonths)=10 or \$(Tmonths)=11 or \$(Tmonths)=12 and count(\$(Tyear)<=3),
sum({<cyear= {\$(Tyear)}, qtr={"2"}>} aggr(DISTINCT Basic,qtr,cyear,OwnerNum,BINum,PCINum,SINum,MDINum,Parcel,PType))*(\$(Tmonths)*2/100 - .06) +
sum({<cyear= {\$(Tyear)}, qtr={"2"}>} aggr(DISTINCT SEF,qtr,cyear,OwnerNum,BINum,PCINum,SINum,MDINum,Parcel,PType))*(\$(Tmonths)*2/100 - .06) +
sum({<cyear= {\$(Tyear)}, qtr={"3"}>} aggr(DISTINCT Basic,qtr,cyear,OwnerNum,BINum,PCINum,SINum,MDINum,Parcel,PType))*(\$(Tmonths)*2/100 - .12) +
sum({<cyear= {\$(Tyear)}, qtr={"3"}>} aggr(DISTINCT SEF,qtr,cyear,OwnerNum,BINum,PCINum,SINum,MDINum,Parcel,PType))*(\$(Tmonths)*2/100 - .12),
))
```

If cyear <= 3 ( below 3 data entry). If cyear data entry is less than or equal to 3 the expression show above will be use. If cyear data entry is more than 3 like 2016, calculation will be ignored (or use different expression). How can I solve this problem?

• ###### Re: how to if count per specific year

In a chart (this includes tables) where cyear is one of your dimensions, use an expression like:

=if (count(cyear) <= 3, Expression1, Expression2)

or omit Expression2 altogether if you want to ignore this year.

You can also use set analysis which offers better performance in case you have a large number of year (which I don't think you have)

• ###### Re: how to if count per specific year

Make CYear as dimension, then use

if count(cYear)<=3 then expression else '' end. Are you looking something or may be I am not understood ?