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

[jonathandienst]

Hi

Just replace the '2011' with one of:

Max(YEAR) - to respect chart dimensions and selections

Max(Total YEAR) - to respect selections that affect YEAR, but ignoring chart dimensions (This is probably what you want)

Max({YEAR=} Total YEAR) - ignore selections on year and chart dimensions
Max({YEAR=,MONTH=,DAY=} Total YEAR) - ignore selections on YEAR, MONTH or DAY

I often use the second. It has the advantage that if the user makes no selection, it contains the current year. If the user DOES make a selection that affects YEAR, it will contain the YEAR for the selection (or the maximum value of YEAR allowed by the selections if more than one year).

Hope that helps

Jonathan

[sparur]

Hello,

At first, I think that you should use Set Analysis, instead of IF statement into Sum() function...

and about your question...

try this:

sum(if(YEAR=$(=max(YEAR)) and...))

[#4]

sparur wrote:
At first, I think that you should use Set Analysis, instead of IF statement into Sum() function...
and about your question...
try this:
sum(if(YEAR=$(=max(YEAR)) and...))

As per your suggestion I exactly did the same thing but it didn't seems to be working. Please check and let me know what may be wrong with the expression. After using this expression I am getting "No data to display"

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

[sparur]

I create a small qvw example. Look at the attachment.

[#6]

Jonathan Dienst wrote:
Hi
Just replace the '2011' with one of:
Max(YEAR) - to respect chart dimensions and selections
Max(Total YEAR) - to respect selections that affect YEAR, but ignoring chart dimensions (This is probably what you want)
Max({YEAR=} Total YEAR) - ignore selections on year and chart dimensions
Max({YEAR=,MONTH=,DAY=} Total YEAR) - ignore selections on YEAR, MONTH or DAY
I often use the second. It has the advantage that if the user makes no selection, it contains the current year. If the user DOES make a selection that affects YEAR, it will contain the YEAR for the selection (or the maximum value of YEAR allowed by the selections if more than one year).
Hope that helps
Jonathan<div></div>

I tried both 1st and 2nd but it didn't seems to be working. Can you please do the required changes in the expression please!

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

[jonathandienst]

Hi

Take off the quote marks from the expression.

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

If that still does not work, test with this to make sure that the problem is not somewhere else

sum(if(FINISH_YEAR=Max(Total YEAR), 1, 0))

You could also check the possible values for FINISH_YEAR (make a list box), and check that Max(Total YEAR) is giving you the value you expect (make a text box with =Max(Total YEAR) as the text)

Jonathan

[#8]

Jonathan Dienst wrote:
Hi
Take off the quote marks from the expression. <blockquote>sum(if(FINISH_YEAR=Max(Total YEAR) and wildmatch(Category,'*Call*') > 0 and (wildmatch([Agreement Met],'no')>0),1,0)) /
sum(if(FINISH_YEAR=Max(Total YEAR) and wildmatch(Category,'*Call*') > 0 ,1,0)) </blockquote>
If that still does not work, test with this to make sure that the problem is not somewhere else<blockquote>sum(if(FINISH_YEAR=Max(Total YEAR), 1, 0))

You could also check the possible values for FINISH_YEAR (make a list box), and check that Max(Total YEAR) is giving you the value you expect (make a text box with =Max(Total YEAR) as the text) </blockquote><div></div>

It seems to work for the Max(Year) but when I use for the Max(Year)-1 it doesn't seems to work. Could you please check and let me know why it is not working.

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

[suniljain]

=sum(if(Stock_Date=MaxStockDate,Stock_Value))

MaxStockDate is varable that contain

MaxStockDate = Max(BIll_Date)

[#10]

Hi Jonathan,

I think once I select the dimension(Year) it does seems to be working properly. Max(Year) seems to be fine but Max(Year)-1 doesn't look good.