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]

Because the example I have given is very simple to achieve by many different ways but the real application is something like below which requires ValueList function.

Here Selected Year = 2013

H21 could be '2013 Sales' and

H24 Could be '2014 Difference'

H38 could be 'Lower values in 2012 by %'

Hope you are having idea why I need ValueList function only..

[trpatel123]

Also, I can handle it by only one expression, if I use multiple ValueList in Dimension.

By your method, I need to use many Expressions...

[sorrakis01]

Hi,

Repeat, one option mayebe Create all the expresions and use condtional show with function SubstringCount

Regards

[Kushal_Chawda]

Using Value list also You need to handle the multiple expression

[trpatel123]

No, It can be done by using single expression with NestedIFs...

I am already getting the result using

Multiple ValueList in Dimension

Nested IFs in a single expression....

But unable to give Year to various headers...

[marcus_sommer]

I'm not sure if valuelist() is here really helpful - if possible I would always try to avoid such calculated dimensions and I think your given example covered not excactly what you described.

Maybe you need more some kind of lookup, maybe with pick(match()).

- Marcus

[trpatel123]

gwassenaar‌‌ can you please look in to this?

[Gysbert_Wassenaar]

See attached example.

[trpatel123]

Thanks Gysbert for you valuable time.

Technically your answer is 100% right but I can't do any changes in my script... My table is having around 25 to 30 KPIs and I can't manage it by script change.

[antoniotiman]

Hi,

see attachment.

I have used ValueLoop(2011,2020) as Year, instead of  ValueList(2011,2012,....,2020)

If You want use ValueList use same sintax

Regards,

Antonio