Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
best wished for 2015 and a lot of Qlikview fun!
My question for today:
I have a table with my customers and I created following fields:
R2012
R2013
R2014
R2015
I'm now building a table with an aggr function, and the dimension to be used in the aggr function is one of the above fields, depending in the value of the function " max(year) ".
Say, the highest selected year is 2014, then the aggr funtion should use the field "R2014" as dimension.
I created a variable, and when I display it in a textbox, it shows R2014 or any other value, depening on what year I selected.
But if I use that variable in de place where I now 'hardcoded' typed 'R2014', my expression doens't give a result.
To be complete:
the function is: -sum(aggr(rangesum(top(sum({$<Jaar={$(=max(Jaar))}>}Omzet),1,rowno()-1))/rangesum(top(sum({$<Jaar={$(=max(Jaar))}>}Omzet),1,noofrows())),R2014)<0.50)
Field R2014 contains per customer their rank for 2014 based on turnover. So whit that expression, I want to find how many customers where needed to reach 50% turnover. It all works, but I want to make it more flexible.
Anyone who knows how to solve this?
Thanks in advance,
Yves
I created a new variable Q2 and the red object seems to work... I think there is a problem with variables' definitions.
KR
Elena
Hello,
you can write 4 functions, as the one you wrote above, one for each of the 4 fields and then use "Enable Conditional" to choose which of the 4 dimensions to use.
You can write GetFieldSelections(Year)=2014 in order to use R2014 and so on...
Elena
Hi Elena,
I don't think that is a solution, because in 2 year, i will have fields names R2016 and R2017, and R2012 and R2013 will no longer exist.
I need to have a formule that will still work in 2017. This can only be achieved if I can make that fieldname in de expression into a variable.
Your solution can work if instead of making the filednames variable, I make then fixed like R1 for the max year, R2 for year-1 and so on.
Thx for your time.
Yves
Yes, it's true..sorry!
Can you attach a sample qvw?
Thanks!
Hi Elena,
I made a work around to this. Your comment about the "enable conditional" pointed me to this.
I changed the field names to R1 for the current year, R2 for previous year, ... .
Then I duplicated my chart as many times as i had years using R1, R2, ... in the expression and then put a conditional show on the chart depending on the selected year ( max(ALL year) - max(year) = 0 for the R1 version, max(ALL year) - max(year) = 1 for the R2 version and so on.
This also works.
Should I have to change anything to the chart later on, I'll have to remeber that it is not 1 chart for all the years, but that these are different chart with a conditional show. I'll put in a text box with a conditional show that only shows when I open to fill with a text to remind me of this.
Thanks for the pointer.
But if you can't resist: I attached a sample file. there is some Dutch in there, but on the left with the green background is a table that shows the result I want for the year 2014, if that year is selected and R2014 (fieldname) is hardcoded in the expression.
With the red background is a copy of the table, but R2014 replaced by a variable, which contains the value 'R2014' but doesn't work.
Kind regards,
Yves
I created a new variable Q2 and the red object seems to work... I think there is a problem with variables' definitions.
KR
Elena
I don't understand this.
What you did works!
I'm almost sure I tried the thing you did, but then again, I wouldn't have posted this question if I really had. It must be the one option I overlooked.
Thanks a lot !
Yves