topic Based on the dimension pick the filed dynamically in QlikView

Based on the dimension pick the filed dynamically

nisha_rai — Wed, 25 Nov 2020 16:16:04 GMT

Hi ,

Need help to resolve the below scenario:

Item	year	Plan	Actual
A	2017	300	0
B	2017	200	0
C	2017	100	0
D	2016	0	100
E	2016	0	200
A	2016	0	250
B	2015	0	150
E	2015	0	225
F	2015	0	325

Based on the above data , i have to create a Chart in which we have to year as a dimension,

if Year=2017 then sum(Plan ) else sum(Actual) even i want the lable name also to change

Like below view

but when user mouse over the 2016 that time it's show Year=2016,Actual=100,item=D

same thing need to replicate in Pivot table that time Year is not in dimension.

Regards,

Nisha

Re: Based on the dimension pick the filed dynamically

Kushal_Chawda — Mon, 08 May 2017 17:22:15 GMT

I am still not clear on requirement. When user do mouse hover on 2017, it show Plan and for 2016 should show Actual?

What do you want to do in pivot? Please elaborate

Re: Based on the dimension pick the filed dynamically

nisha_rai — Mon, 08 May 2017 17:43:47 GMT

1st Requirement is create a Bart chart in which Year as a dimension, where 2017 that time it pick Plan value else rest of other year it should show Actual Value

2nd requirenment when i will mouse hover the bar of 2017 expression lable should be Plan rest of the Year Mouse hover should show Actual

3rd requirement i want to create a Pivot table because i need to show year as a column and values segregate based on the Actual and Plan

Like that

Re: Based on the dimension pick the filed dynamically

neelamsaroha157 — Mon, 08 May 2017 18:04:13 GMT

Does something like this works for you

Re: Based on the dimension pick the filed dynamically

nisha_rai — Tue, 09 May 2017 04:22:07 GMT

Thank Neelam, it's complete 1 requirement , but if for 2017 we need to show Actual and Plan that time it doesn't work.

Re: Based on the dimension pick the filed dynamically

Kushal_Chawda — Tue, 09 May 2017 04:57:57 GMT

like this

Data:

LOAD Item,

year,

Plan,

Actual

FROM

[https://community.qlik.com/thread/259672]

(html, codepage is 1252, embedded labels, table is @1);

Left Join

LOAD max(year) as year,

1 as Flag

Resident Data;

Final:

NoConcatenate

LOAD *,

if(Flag=1,Plan,Actual) as Plan_Actual,

if(Flag=1,'Plan','Actual') as Label

Resident Data;

DROP Table Data;

Bar chart expression:

=dual('Year:'&year&chr(10)& 'Item:'&Item&chr(10) & Label&':'& sum(Plan_Actual),sum(Plan_Actual))

For this expression check text as pop up

chart properties->Presentation-> uncheck pop up label