Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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)
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 ?