Hard coded year to dynamic year

[#1]

Hi

Please make the hard coded year in the expression in to dynamic(Year as the selection). I know it is very simple to do so but it is not working.

i,e YEAR is hardcode here, I want to make it as Max(YEAR ). So that by default it will show the current year and if multiple year are selected it will show MAX(YEAR ) in selected.

Expression:

=sum(if(YEAR='2011' and wildmatch(Category,'*Call*') > 0 and (wildmatch([Agreement Met],'no')>0),1,0)) /
sum(if(YEAR='2011' and wildmatch(Category,'*Call*') > 0 ,1,0))

[#2]

Sunil Jain wrote:
=sum(if(Stock_Date=MaxStockDate,Stock_Value))
MaxStockDate is varable that contain
MaxStockDate = Max(BIll_Date)<div></div>

Sorry I tried it but didn't seems to be working.

 =sum(if(YEAR=MaxYear and wildmatch(Category,'*Call*') > 0 and (wildmatch([Agreement Met],'no')>0),1,0)) / sum(if(YEAR=MaxYear and wildmatch(Category,'*Call*') > 0 ,1,0))

MaxYear is varable that contain

MaxYear = Max(FINISH_YEAR)

PrvYear is varable that contain

Prv = Max(FINISH_YEAR)-1

[jonathandienst]

Rikab

When you make a selection on YEAR, Max(YEAR) or Max(TOTAL Year) will contain the selected year, and your expresssion will return data that is related to that year. Max(YEAR)-1 refers to a year that is outside the year selection and will therefore return nothing. You can verify this by selecting two consecutive years (say 2010 and 2009).

This is where set analysis comes in (apart from being more efficient). This might work:

Count({<YEAR=,FINISH_YEAR={'$(=Max(Total YEAR)-1)'}, Category={"=Wildmatch(Category,'*Call*') > 0"}, [Agreement Met]={"=Wildmatch([Agreement Met],'no') > 0"}>} Category) /
Count({<YEAR=,FINISH_YEAR={'$(=Max(Total YEAR)-1)'}, Category={"=Wildmatch(Category,'*Call*') > 0"}>} Category)

Jonathan

Edit: Fixed missing opening <

[#4]

Hi Jonathan

When I select 2011 I want the expression to filter for 2010, if I select 2010 i want the expression to filter for 2009. I agree with you that if I select least year then it is not going to show anything. But atleast when I select for 2010 it should show for 2009. When I hardcode it, it is working fine.

Please check and let me know it is very urgent.

[jonathandienst]

Rikab

That is what the expression in my earlier post should do

Jonathan

[#6]

What I am not able to understand is that I dont have YEAR field but you use YEAR field. I have only FINISH_YEAR other than that I have nothing.

I am really sorry for the inconvenience.

[#7]

Hi

Please look into the below application which works when I don't select the dimension(YEAR) value. Without any selection it works fine but when I select it doesn't work. Can you please do the required changes in this expression itself.

I am not able to understand why you are using YEAR when only FINISH_YEAR is available.

=sum(if(FINISH_YEAR=Max(Total FINISH_YEAR)-2 and wildmatch(Category,'*Call*') > 0 and (wildmatch([Agreement Met],'no')>0),1,0)) /
sum(if(FINISH_YEAR=Max(Total FINISH_YEAR)-2 and wildmatch(Category,'*Call*') > 0 ,1,0))

[#8]

Jonathan Dienst wrote:
Rikab
That is what the expression in my earlier post should do
Jonathan<div></div>

Still I tried with what you have given but still it didn't work

[#9]

The Year field that they are using in the expression is equivalent to the Date/Year Field in your table that you use..

lets say I have a table

Year(DateFinished) as Finish_Date. You will use Finish_Date instead of Year(w/c are in the posts).

[#10]

Yes thats the same question I am asking too. When Finish_Year is there why they are using Year as a field and also Finish_Year as a field in the expression.

[#11]

they're just using it as a description not as the field per se..