topic ValueList in QlikView

ValueList

trpatel123 — Fri, 31 Jul 2015 11:47:15 GMT

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

Re: ValueList

sorrakis01 — Fri, 31 Jul 2015 11:52:38 GMT

Hi,

One option is, create all the expresions and use conditional to show or unshow the expresions based on selecttion Year.

Regards

Re: ValueList

Kushal_Chawda — Fri, 31 Jul 2015 11:57:46 GMT

Go to Expression Label. Put below Expression

Create Pivot

Dimension: Customer

Expressions :

sum(Sales)

Sum(Qty)

Now for Each Expression Label but below Expression

=Year &' Sales'

=Year &' Qty'

Re: ValueList

marcus_sommer — Fri, 31 Jul 2015 12:00:25 GMT

You could use something like this:

exp: sum({< Year = {"$(=max(Year))"} >} Sales) // current year based on selection

label: = max(Year) & ' Sales'

exp: sum({< Year = {"$(=max(Year) - 1)"} >} Sales) // previous year to curent year

label: = max(Year) - 1 & ' Sales'

- Marcus

Re: ValueList

trpatel123 — Fri, 31 Jul 2015 12:03:18 GMT

I need solution only by ValueList as there are many KPIs need to be shown and simple expression would not work.

Re: ValueList

sunny_talwar — Fri, 31 Jul 2015 12:04:00 GMT

This?

Look at the attached qvw file.

Best,

Sunny

Re: ValueList

trpatel123 — Fri, 31 Jul 2015 12:04:10 GMT

Thanks Marcus for your solution but real application is having many KPIs which is not easily shown by the answer given by you.

I need solution only by ValueList function.

Re: ValueList

trpatel123 — Fri, 31 Jul 2015 12:04:48 GMT

Thanks Sunny but need answer by only ValueList Function...

Re: ValueList

sorrakis01 — Fri, 31 Jul 2015 12:08:23 GMT

Hi,

What do you mean with ValueList function?

GetFieldSelecctons?

Regards

Re: ValueList

sunny_talwar — Fri, 31 Jul 2015 12:08:46 GMT

Not sure I understand? If you have the data setup like this for all your KPIs, why do you have to use ValueList and not the above mentioned approach by all of us?

Re: ValueList

trpatel123 — Fri, 31 Jul 2015 12:14:03 GMT

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..

Re: ValueList

trpatel123 — Fri, 31 Jul 2015 12:20:28 GMT

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...

Re: ValueList

sorrakis01 — Fri, 31 Jul 2015 12:23:11 GMT

Hi,

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

Regards

Re: ValueList

Kushal_Chawda — Fri, 31 Jul 2015 12:24:59 GMT

Using Value list also You need to handle the multiple expression

Re: ValueList

trpatel123 — Fri, 31 Jul 2015 12:29:16 GMT

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...

Re: ValueList

marcus_sommer — Fri, 31 Jul 2015 12:29:26 GMT

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

Re: ValueList

trpatel123 — Fri, 31 Jul 2015 12:36:25 GMT

gwassenaar‌‌ can you please look in to this?

Re: ValueList

Gysbert_Wassenaar — Fri, 31 Jul 2015 13:22:52 GMT

See attached example.

Re: ValueList

trpatel123 — Fri, 31 Jul 2015 13:33:51 GMT

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.

Re: ValueList

antoniotiman — Fri, 31 Jul 2015 14:08:28 GMT

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