topic Re: Using an expression to point to a fieldname in QlikView

Using an expression to point to a fieldname

Tue, 06 Jan 2015 15:12:52 GMT

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

Re: Using an expression to point to a fieldname

Anonymous — Tue, 06 Jan 2015 19:19:41 GMT

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

Re: Using an expression to point to a fieldname

Wed, 07 Jan 2015 10:07:15 GMT

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

Re: Using an expression to point to a fieldname

Anonymous — Wed, 07 Jan 2015 13:30:28 GMT

Yes, it's true..sorry!

Can you attach a sample qvw?

Thanks!

Re: Using an expression to point to a fieldname

Wed, 07 Jan 2015 14:28:55 GMT

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

Re: Using an expression to point to a fieldname

Anonymous — Wed, 07 Jan 2015 14:58:02 GMT

I created a new variable Q2 and the red object seems to work... I think there is a problem with variables' definitions.

Elena

Re: Using an expression to point to a fieldname

Wed, 07 Jan 2015 15:54:43 GMT

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