How to insert dynamic values in valueList() function?

[ananyaghosh]

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?

[MayilVahanan]

Hi

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

[marcus_sommer]

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

[tresesco]

Or,

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

[ananyaghosh]

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.

[MayilVahanan · Accepted solution]

Hi

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

[ananyaghosh]

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