topic Hard coded year to dynamic year in QlikView

Hard coded year to dynamic year

Mon, 17 Jan 2011 09:11:09 GMT

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

Hard coded year to dynamic year

jonathandienst — Mon, 17 Jan 2011 09:32:49 GMT

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

Hard coded year to dynamic year

sparur — Mon, 17 Jan 2011 09:35:51 GMT

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

Hard coded year to dynamic year

Mon, 17 Jan 2011 12:12:11 GMT


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

Hard coded year to dynamic year

sparur — Mon, 17 Jan 2011 12:18:24 GMT

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

Hard coded year to dynamic year

Mon, 17 Jan 2011 12:28:37 GMT


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

Hard coded year to dynamic year

jonathandienst — Mon, 17 Jan 2011 14:30:48 GMT

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

Hard coded year to dynamic year

Mon, 17 Jan 2011 15:50:51 GMT


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

Hard coded year to dynamic year

suniljain — Mon, 17 Jan 2011 15:53:26 GMT

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

MaxStockDate is varable that contain

MaxStockDate = Max(BIll_Date)

Hard coded year to dynamic year

Mon, 17 Jan 2011 15:58:51 GMT

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.

Hard coded year to dynamic year

Mon, 17 Jan 2011 16:12:08 GMT


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

Hard coded year to dynamic year

jonathandienst — Mon, 17 Jan 2011 17:13:23 GMT

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 <

Hard coded year to dynamic year

Mon, 17 Jan 2011 17:49:27 GMT

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.

Hard coded year to dynamic year

jonathandienst — Mon, 17 Jan 2011 17:58:37 GMT

Rikab

That is what the expression in my earlier post should do

Jonathan

Hard coded year to dynamic year

Mon, 17 Jan 2011 18:02:54 GMT

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.

Hard coded year to dynamic year

Mon, 17 Jan 2011 18:29:48 GMT

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

Hard coded year to dynamic year

Tue, 18 Jan 2011 03:57:39 GMT


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

Hard coded year to dynamic year

Tue, 18 Jan 2011 04:07:03 GMT

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

Hard coded year to dynamic year

Tue, 18 Jan 2011 06:14:36 GMT

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.

Hard coded year to dynamic year

Tue, 18 Jan 2011 06:21:29 GMT

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