Based on the dimension pick the filed dynamically

[nisha_rai]

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

[Kushal_Chawda]

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

[nisha_rai]

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

[neelamsaroha157]

Does something like this works for you

[nisha_rai]

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

[Kushal_Chawda]

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