how to if count per specific year - Qlik Community

Report Inappropriate Content · ‎2016-01-12

For example I have this data.

cyear pin tax

2015 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?

Peter_Cammaert · ‎2016-01-12

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)

manojkulkarni · ‎2016-01-12

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 ?