bar chart functionality

[ritumishra01]

hi, 

i have two tables final and final_01. 

Idea:
LOAD

idea_number,
idea_number as idea_metric,
dmnd_number,
idea_sys_id,
idea_sys_created_by,
idea_sys_created_on,
user_name,
idea_short_description,
idea_u_business_area_group,
idea_u_business_area,
idea_state,
if(idea_state = '1' or idea_state = '2' or idea_state = '-5','submitted',
if(idea_state = '3','completed',
if(idea_state ='7','rejected'))) as idea_status,
//idea_sys_created_on,
dmnd_sys_created_on as idea_dmnd_sys_created_on
FROM [lib://QlikSenseSupportFiles/Intake Dashboard Files/idea_demand_01.QVD]
(qvd)
where idea_sys_created_on >= '2023-01-01 00:00:00' ;
//and dmnd_sys_created_on >= '2023-01-01 00:00:00';
outer join (Idea)
Demand:
LOAD
dmnd_number,
dmnd_number as dmnd_metric,
prj_number,
dmnd_state,
dmnd_start_date,
dmnd_approved_end_date as dmnd_end_date,
if(dmnd_state = '1' or dmnd_state = '2' ,'submitted',
if(dmnd_state = '9' ,'completed',
if(dmnd_state ='3' or dmnd_state = '-4','under_review',
if(dmnd_state = '8','in_progress' ,
if (dmnd_state ='7' or dmnd_state = '10' or dmnd_state = '5','rejected'))))) as dmnd_status,
u_opid_id_dmnd,
dmnd_sys_created_by,
__KEY_result
FROM [lib://QlikSenseSupportFiles/Intake Dashboard Files/demand_project_01.QVD]
(qvd)
where
dmnd_sys_created_on >= '2023-01-01 00:00:00';

outer join(Idea)
Project:
add LOAD
prj_number,
prj_number as prj_metric,
prj_state,
work_start as prj_start_date,
work_end as prj_end_date,
sys_created_on as project_sys_created_on,
// __KEY_result,
u_opid_id_prj,
if(num(prj_state) = 1 or num(prj_state) = 2 or num(prj_state) = -5,'in_progress',
if(num(prj_state)= 4 or num(prj_state)= 7 or num(prj_state) =15 ,'rejected',
if(num(prj_state) = 3,'completed'))) as prj_Status

FROM [lib://QlikSenseSupportFiles/Intake Dashboard Files/project_01.QVD]
(qvd)
where
//not Exists(prj_number) and
sys_created_on >= '2023-01-01 00:00:00';

left join (Idea)
Demand_manager:
LOAD
link_u0,
IT_Resource,
__KEY_result
FROM [lib://QlikSenseSupportFiles/Intake Dashboard Files/dmnd_demand_manager_new.QVD]
(qvd);

main:
load
idea_number,
dmnd_number,
prj_number,
idea_metric,dmnd_metric,prj_metric,
user_name,prj_Status,dmnd_status,idea_status,
idea_number &'|'& dmnd_number &'|'& prj_number as key1,
idea_status &'|'& dmnd_status &'|'& prj_Status as full_Status,
idea_sys_created_on,
if((not isnull(prj_number) and len(prj_number)>1),prj_Status,
if(((not IsNull(dmnd_number)) or (len(dmnd_number)>1))
//and (isnull(prj_number)) or (len(prj_number)<1)
and ((len(dmnd_status)>1) or (not isnull(dmnd_status))),dmnd_status,
if(len(idea_status)>1,idea_status))) as status_1,

if(len(dmnd_number)>1 or prj_number = '%-%',dmnd_state) as demand_Status,
if(len(prj_number)>0,prj_Status,
if(len(dmnd_number)>0 ,dmnd_status,
if(len(idea_number)>0,idea_status))) as status,
idea_short_description,__KEY_result,IT_Resource,
idea_u_business_area_group,
idea_u_business_area
Resident Idea;
//drop table Idea;

final:
load
idea_sys_created_on,
IT_Resource,
//__KEY_result,
//dmnd_metric,prj_metric,
(idea_metric &'|'&dmnd_metric &'|'&prj_metric) as key,
idea_number,
dmnd_number,
prj_number,
user_name,
full_Status,
status,status_1,
key1,
SubField(full_Status,'|',-1) as status_2,
SubField(full_Status,'|',-2) as status_3,
SubField(full_Status,'|',-3) as status_4,
if(isnull(status_1),SubField(status_1,' ',-2)) as new_status,
idea_short_description,
idea_u_business_area_group,
idea_u_business_area
resident main;

//drop tables main;
drop tables Idea,main;

Sys_USer:
LOAD
u_opid_id,
user_name,
test_username,
sys_id,
first_name,
last_name,
full_name,
sys_created_on,
email
FROM [lib://QlikSenseSupportFiles/Intake Dashboard Files/Sys_User_01.QVD]
(qvd);
//where sys_created_on >= '2023-01-01 00:00:00';;

//outer join (Demand)
Sys_demand_manager:
Load
u_opid_id as u_opid_id_dmnd,
sys_id as IT_Resource,
full_name as full_name_dmnd

resident Sys_USer;

//exit script;

//exit script;
//left join(final)
AllIdea_:
LOAD
idea_number as idea_metric,
// dmnd_number,
idea_sys_id,
// idea_sys_created_by,
// idea_sys_created_on,
// user_name,
// idea_short_description,
// idea_u_business_area_group,
// idea_u_business_area,
//idea_state,
idea_closed_at,
if(idea_state = '1' or idea_state = '2' or idea_state = '-5','submitted') as idea_sub_metric,
if(idea_state = '1' or idea_state = '2' or idea_state = '-5','submitted',
if(idea_state = '3','completed',
if(idea_state ='7','rejected'))) as status_m,
(idea_closed_at-idea_sys_created_on) as mi_duration,
idea_sys_created_on as idea_sys_created_on_metric,
dmnd_sys_created_on as idea_dmnd_sys_created_on
FROM [lib://QlikSenseSupportFiles/Intake Dashboard Files/idea_demand_01.QVD]
(qvd)
where idea_sys_created_on >= '2023-01-01 00:00:00' ;

left join (AllIdea_)
Idea_metric:
LOAD
idea_number as idea_metric,

//idea_number,
idea_demand_composite,
idea_sys_created_on as idea_sys_created_on_metric,
// subfield( idea_demand_composite,'D',1) as dmnd_number,
mi_value as idea_mi_value,
if (mi_value ='Accepted' or mi_value ='Submitted','submitted',
if(mi_value='Closed Complete','completed')) as status_m,
//avg(if (mi_value ='Accepted' or mi_value ='Submitted',mi_value)) as average_submitted,
mi_end as idea_mi_end,
// sys_created_on as mi_start,
//idea_closed_at as mi_end,
//(idea_closed_at -idea_sys_created_on) as mi_duration,
(mi_end - mi_start) as mi_duration,
mi_start as idea_mi_start,
__KEY_result as key_idea
FROM [lib://QlikSenseSupportFiles/Intake Dashboard Files/idea_metric_01.QVD]
(qvd)
where idea_sys_created_on >= '2023-01-01 00:00:00' ;

left join (AllIdea_)
idea_demand:
LOAD
link_u0,
value_u0 as idea_demand,
__KEY_result as key_idea
FROM [lib://QlikSenseSupportFiles/Intake Dashboard Files/idea_demand.QVD]
(qvd);

outer join (AllIdea_)
Demand_metric:
LOAD
dmnd_number as dmnd_metric,
mi_start as dmnd_mi_start,
(mi_end - mi_start) as mi_duration,
mi_end as dmnd_mi_end,
mi_value as dmnd_mi_value,
//idea_link
dmnd_sys_id as idea_demand,
//prj_link
dmnd_project,
if(mi_value ='Draft' or mi_value ='Submitted','submitted' ,
if(mi_value ='Approved','in-progress',
if(mi_value='Qualified'or mi_value='Screening','under-review',
if( mi_value='Completed','completed',
if(mi_value = 'Deferred' or mi_value= 'Rejected', 'rejected'))))) as status_m,

if(mi_value ='Draft' or mi_value ='Submitted','submitted') as dmnd_misubmitted ,
if(mi_value ='Approved','In-Progress') as dmnd_inprogress,
if(mi_value='Qualified'or mi_value='Screening','under-review') as dmnd_mireview,
if( mi_value='Completed','Completed')as completed,
if(mi_value = 'Deferred' or mi_value= 'Rejected', 'rejected')as dmndrejected

//if(mi_value ='Draft' or mi_value ='Submitted',avg(mi_value)) as dmnd_submitted

FROM [lib://QlikSenseSupportFiles/Intake Dashboard Files/result_demand_metric.QVD]
(qvd)
//where dmnd_sys_created_on >= '2023-01-01 00:00:00' ;
;

// dmnd_metric:
// load
// dmnd_number,
// num(dmnd_mi_duration) as dmnd_mi_duration,
// dmnd_mivalue,
// dmnd_misubmitted ,
// dmnd_inprogress,
// dmnd_mireview,
// dmndrejected,
// avg(if(dmnd_mivalue ='Draft' or dmnd_mivalue ='Submitted',dmnd_mi_duration)) as dmnd_submitted
// resident Demand_metric;
// //Group by dmnd_number ;
// drop table Demand_metric ;

outer join (AllIdea_)
Project_metric:
LOAD
prj_number as prj_metric,
prj_mi_start,
prj_mi_end,
pmp_sys_id as dmnd_project,
pmp_sys_created_on,
pmp_state as prj_mi_value,
pmp_state,
if(pmp_state ='1' , 'in-progres' ,
if(pmp_state ='2','in-progress',
if(pmp_state='-5','in-progress',
if(pmp_state='3','completed',
if(pmp_state = '4' or pmp_state= '7'or pmp_state= '15','rejected'))))) as status_m,

if(pmp_state ='1' , 'Submitted' ) as prj_submitted,
if(pmp_state ='2','In-Progress')as prj_inprogress,
if(pmp_state='-5','Under-Review' )as prj_Review,
if( pmp_state='3','Completed') as prj_completed,
//if(pmp_state = '4' or pmp_state= '7', '15'
//if(pmp_state ='1' , avg(mi_value) ) as prj_submitted,
(prj_mi_end - prj_mi_start) as mi_duration

FROM [lib://QlikSenseSupportFiles/Intake Dashboard Files/result_prj_metric.QVD]
(qvd)

where pmp_sys_created_on >= '2023-01-01 00:00:00' ;
;
// prj_metric:
// LOAD
// prj_number,
// prj_mi_start,
// prj_mi_end,
// pmp_sys_created_on,
// prj_mi_value,
// prj_mivalue,
// prj_submitted,
// prj_inprogress,
// prj_Review,
// prj_completed,
// if(prj_mi_value = 'In-Progress',prj_mi_duration) as prj_misubmittedvalue,
// prj_mi_duration

// resident Project_metric;
// drop table Project_metric;

final_01:
load
//idea_mi_value,idea_number,
idea_metric,dmnd_metric ,prj_metric,
pmp_state,
(idea_metric&'|'&dmnd_metric &'|'&prj_metric) as key,
//(idea_metric &'|'&dmnd_metric &'|'&prj_metric) as metrickey,
//autonumber(idea_number &'|'&dmnd_number &'|'&prj_number) as test_metric,
mi_duration,status_m,
pmp_sys_created_on
//mi_duration_demand,mi_duration_idea,mi_duration_project,
//status_metric_project,status_metric_demand,status_metric_idea
resident AllIdea_;
drop table AllIdea_;

// outer join (final_01)
// final_idea:
// load
// //idea_closed_at as mi_end,
// (idea_closed_at-idea_sys_created_on) as mi_duration,
// idea_closed_at,idea_sys_created_on,
// idea_number,
// idea_status as status_m
// resident final;

exit script;

front end -- selection in idea_number dmnd_number and prj_number are working . 

while i select value in table it is not reflecting in bar chart.