topic How to insert dynamic values in valueList() function? in QlikView

How to insert dynamic values in valueList() function?

ananyaghosh — Mon, 25 May 2015 07:59:55 GMT

Hi,

I want to create dynamic values for ValueList() function like this:

ValueList("(=max(Year)) & '-' & 'Q2'", "(=max(Year))& '-' & 'Q1')", "(=max(Year)-1)& '-' & 'Q1'")

So ultimately the values will be like this:-

ValueList('2015-Q2', '2014-Q1', '2015-Q1')

So How Can I do that?

Re: How to insert dynamic values in valueList() function?

MayilVahanan — Mon, 25 May 2015 08:08:32 GMT

Create 3 variables

vMaxYearQ2

=max(year) & '-' & 'Q2

vMaxYearQ1

=max(year) & '-' & 'Q1'

vMaxYear_1Q2

=max(year)-1 & '-' & 'Q1'

Then, use ValueList (vMaxYearQ2, vMaxYearQ1,vMaxYear_1Q2) as Dimension

Hope it helps

Re: How to insert dynamic values in valueList() function?

marcus_sommer — Mon, 25 May 2015 08:16:27 GMT

I think you need $-sign expansion to build the valuelist-values, like:

valuelist('$(=max(year) & '-Q2')', .... // maybe there are some adjustments necessary for the quotes-syntax

The Magic of Dollar Expansions

- Marcus

Re: How to insert dynamic values in valueList() function?

tresB — Mon, 25 May 2015 08:19:17 GMT

Or,

=ValueList($(=chr(39)&max(Year) & '-' & 'Q2'&chr(39)&','&chr(39)&max(Year)& '-' & 'Q1'&Chr(39)&','&chr(39)&(max(Year)-1)& '-' & 'Q1'&chr(39) ))

Re: How to insert dynamic values in valueList() function?

ananyaghosh — Mon, 25 May 2015 09:16:43 GMT

Hi,

I have used this expression :

if(ValueList(vMaxYearQ2, vMaxYearQ1,vMaxYear_1Q2)=vMaxYearQ2,only({$<Year={"$(=Max(Year))"}, Quarter={"Q1"}>}[KPI Data]),
if(ValueList(vMaxYearQ2, vMaxYearQ1,vMaxYear_1Q2)=vMaxYearQ2,only({$<Year={"$(=Max(Year)1-)"}, Quarter={"Q1"}>}[KPI Data]),
if(ValueList(vMaxYearQ2, vMaxYearQ1,vMaxYear_1Q2)=vMaxYearQ2,only({$<Year={"$(=Max(Year)-1)"}, Quarter={"Q2"}>}[KPI Data])))

But it does not work.

Can u Check it? !

I have attached my QVW fileand also I have attached my Excel file. Please check it.

Re: How to insert dynamic values in valueList() function?

MayilVahanan — Mon, 25 May 2015 09:21:17 GMT

Try like this

if(ValueList(vMaxYearQ2, vMaxYearQ1,vMaxYear_1Q2)=vMaxYearQ2,only({$<Year={"$(=Max(Year))"}, Quarter={"Q2"}>}[KPI Data]),

if(ValueList(vMaxYearQ2, vMaxYearQ1,vMaxYear_1Q2)=vMaxYearQ1,only({$<Year={"$(=Max(Year))"}, Quarter={"Q1"}>}[KPI Data]),

if(ValueList(vMaxYearQ2, vMaxYearQ1,vMaxYear_1Q2)=vMaxYear_1Q2,only({$<Year={"$(=Max(Year)-1)"}, Quarter={"Q2"}>}[KPI Data]))))

Re: How to insert dynamic values in valueList() function?

ananyaghosh — Mon, 25 May 2015 09:49:25 GMT

Hi,

Can U check the attached QVW file? I want to make bold of all Header text that means I wan to make bold the text 'Portfolio', 'Benchmark', '2015 - Q1', '2015- Q2' and also omit the rows whose value will be null