ValueList

[trpatel123]

I have below Data

Load * Inline
[
  Customer, Year, Qty, Sales
  A,  2011, 10, 1000
  A,  2012, 20, 2000
  A,  2013, 30, 3000
  A,  2014, 40, 4000
  A,  2015, 50, 5000
  A,  2016, 60, 6000
  A,  2017, 70, 7000
  B,  2011, 15, 1500
  B,  2012, 25, 2500
  B,  2013, 35, 3500
  B,  2014, 45, 4500
  B,  2015, 55, 5500
  B,  2016, 65, 6500
  B,  2017, 75, 7500
];

I want to create a Pivot table with Dynamic Heading

If user select Year then need Header name dynamically...

If user select 2012, I need below table

Customer     2012 Sales     2012 Qty     2011 Sale     2011 Qty

A                 2000               20              1000            10

B                 2500               25              1500            15

Important thing here is Dynamic Header... I don't want Sales and Qty only. I want Year also.

The actual pivot is having many KPIs so, can't do Max(Year) & 'Sales' and Max(Year) & 'Qty'...

Hope I am clear in my question.

I need this by ValueList Function...

Thanks

[trpatel123]

Thanks but this is not what I am looking for..

My question belongs to heading names... 2012 Sales, 2012 Qty.. .Not only Sales and Qty

In short my question is....Whether is it possible to use ValueList Function with Dynamic Value and a string concatenated to this dynamic values..

i.e.

ValueList($(=Max(Year))&' Something',$(-Max(Year)+1) & ' Something')

[Gysbert_Wassenaar]

The I humbly suggest to learn how to do such script changes. The number of KPI's is irrelevant if your table looks like the one you posted.

[marcus_sommer]

You could have dynamic values within the valuelist but you need to consider the syntax from $-expansion:

valuelist('$(=Max(Year)) Something', '$(=Max(Year)-1) Something')

But I agree with gwassenaar that you should try to solve it within the script.

- Marcus