Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Hard coded year to dynamic year

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


21 Replies
MVP
MVP

Hard coded year to dynamic year

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
sparur
Valued Contributor II

Hard coded year to dynamic year

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

Not applicable

Hard coded year to dynamic year


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
Valued Contributor II

Hard coded year to dynamic year

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

Not applicable

Hard coded year to dynamic year


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


MVP
MVP

Hard coded year to dynamic year

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

Hard coded year to dynamic year


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
Honored Contributor

Hard coded year to dynamic year

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

MaxStockDate is varable that contain

MaxStockDate = Max(BIll_Date)

Not applicable

Hard coded year to dynamic year

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.

Community Browser