topic Re: single bar chart to include bars of multiple indicator variables in QlikView

single bar chart to include bars of multiple indicator variables

kmstephenson — Thu, 30 Oct 2014 18:37:45 GMT

I have a wide dataset that includes many chronic condition indicators, and I want to see all of these on the same bar chart.

For example:

Patient_ID Start_Dt End_Dt chf_ind copd_ind diabetes_ind ckd_ind ..... etc

1 1/1/2012 12/31/2012 1 0 0 0

2 1/1/2012 12/31/2012 0 0 1 1

3 1/1/2012 12/31/2012 1 1 1 1

What I did is keep this dataset with all indicator variables as the fact table and then transposed the data into 1 row per patient per time frame per condition (indicator variable=1).

For, example:

Patient_ID Start_Dt End_Dt condition

1 1/1/2012 12/31/2012 CHF

2 1/1/2012 12/31/2012 DIABETES

2 1/1/2012 12/31/2012 CKD

3 1/1/2012 12/31/2012 CHF

3 1/1/2012 12/31/2012 COPD

3 1/1/2012 12/31/2012 DIABETES

I used the transposed variable condition for the bar chart to be able to see in descending order the frequency of conditions. However, if I select any condition within the bar chart, it does not show me what other conditions these patients have. For example, I want to be able to select the CHF bar and see that of patients with CHF, the other coexisting conditions they have are COPD and DIABETES. I had to create listboxes and buttons to select the indicator variable from the fact table to update the bar chart with this information.

Do you have any recommendations for another way of doing this? Where you have a number of indicator variables that you want to see on the same chart like this?

Thank you!

Re: single bar chart to include bars of multiple indicator variables

mark_casselman — Thu, 30 Oct 2014 23:33:01 GMT

Hi,

We did this a while ago. The trick is to make a data island with conditions, and select all customers that have that condition with the P()-function.

Script:

data:

LOAD * INLINE [

Patient_ID, Start_Dt , End_Dt , condition

1 , 1/1/2012 , 12/31/2012 , CHF

2 , 1/1/2012 , 12/31/2012 , DIABETES

2 , 1/1/2012 , 12/31/2012 , CKD

3 , 1/1/2012 , 12/31/2012 , CHF

3 , 1/1/2012 , 12/31/2012 , COPD

3 , 1/1/2012 , 12/31/2012 , DIABETES

];

condition_data_island:

LOAD distinct condition as selected_condition

resident data;

Chart (the expression is in the title):

Mark

Re: single bar chart to include bars of multiple indicator variables

kmstephenson — Fri, 31 Oct 2014 13:50:24 GMT

Hi Mark,

Could you provide the qvw of what you pasted in the response above? It isn't working for me so I wanted to double check a few things.

Thank you!

Re: Re: single bar chart to include bars of multiple indicator variables

mark_casselman — Fri, 31 Oct 2014 22:33:13 GMT

Hi,

Here it is.

Mark

Re: Re: single bar chart to include bars of multiple indicator variables

kmstephenson — Mon, 03 Nov 2014 16:06:54 GMT

Okay, thanks! Do you know of any way to use this approach with a cycle group? For example, I happen to have 5 different ways that chronic conditions are measured (the condition indicators above are grouped into frailty conditions, conditions that result in high mortality rates, currently managed, hccs etc), so I was using a cycle group that had these different condition type fields.

Re: single bar chart to include bars of multiple indicator variables

mark_casselman — Mon, 03 Nov 2014 22:46:35 GMT

KM,

I'm not sure I understand your additional question. Do you still want te select a specific condition (like DIABETES) and only see the 'related' conditions from one group (like FRAILTY), or do you want to select a group of conditions and see all related conditions ? Perhaps, you could give me a more detailed example of data and expected result ?

Greets,

Mark

Re: single bar chart to include bars of multiple indicator variables

kmstephenson — Fri, 07 Nov 2014 15:56:54 GMT

Hi Mark,

From the example above, let's just say CHF and COPD belonged to one condition type, and DIABETES and CKD had other characteristics belonging to another condition type. I would have two separate data tables, one for each condition type. For example:

data_conditiontype1:

LOAD * INLINE

[ Patient_ID, Start_Dt , End_Dt , conditiontype1

1 , 1/1/2012 , 12/31/2012 , CHF

3 , 1/1/2012 , 12/31/2012 , CHF

3 , 1/1/2012 , 12/31/2012 , COPD

];

data_conditiontype2

LOAD * INLINE

[ Patient_ID, Start_Dt , End_Dt , conditiontype2

2 , 1/1/2012 , 12/31/2012 , DIABETES

2 , 1/1/2012 , 12/31/2012 , CKD

3 , 1/1/2012 , 12/31/2012 , DIABETES

];

Condition types 1 and two would be in the same cycle group. Does that make sense?

Re: single bar chart to include bars of multiple indicator variables

mark_casselman — Mon, 10 Nov 2014 07:49:39 GMT

Hi KM,

If these groups are relevant, i would add them to your data. You can add them in de data itself:

LOAD * INLINE

[ Patient_ID, Start_Dt , End_Dt , conditiontype, conditiongroup

1 , 1/1/2012 , 12/31/2012 , CHF, Group1

3 , 1/1/2012 , 12/31/2012 , CHF, Group1

3 , 1/1/2012 , 12/31/2012 , COPD, Group1

];

Then you can use the conditiongroup var directly in your charts or as a cyclic group. (extra remark : as you can see you should change the var names conditiontype1 and conditiontype2 in conditiontype so that conditiontypes of both tables end up in the same var in one table).

If you have a lot of data, you can also load a seperate table like this (QV will link the tables automatically):

LOAD * Inline [

conditiontype, conditiongroup

CHF, Group1

COPD,Group1

DIABETES, Group2

CKD,Group2

];

Mark

Re: single bar chart to include bars of multiple indicator variables

kmstephenson — Mon, 10 Nov 2014 20:42:26 GMT

Hi Mark,

What would the cycle group be? I want to be able to cycle through conditiongroup to see condition types all in one table like the original example you posted above. From my understanding, if conditiongroup was the cycle group, then the bar chart would contain only 1 bar for Group 1, and cycle through to 1 bar for Group 2, etc. I want all conditions for each condition group to be shown when that condition group is selected

Re: Re: single bar chart to include bars of multiple indicator variables

mark_casselman — Wed, 12 Nov 2014 22:28:31 GMT

KM,

You were correct to put the condition_types in 2 different variables to enable cycle groups.

I added them to the example.

Ok ?

Mark

Re: Re: single bar chart to include bars of multiple indicator variables

kmstephenson — Thu, 20 Nov 2014 16:48:06 GMT

Hi Mark,

This works but only if you use the list box to choose the condition. Ideally, we would like to be able to select any condition in the bar chart for condition type1 (we have between 20 and 70 conditions for each condition type) and have the bar chart update to all comorbid conditions for the condition selected (versus having to use a list box to select the conditions). Is this an easy fix?

Thanks!

Re: Re: Re: single bar chart to include bars of multiple indicator variables

mark_casselman — Thu, 20 Nov 2014 21:02:27 GMT

Hi KM,

Took some time, but can be solved with 'alternate states' instead of data islands.

See attached updated file. Select a condition form the cycle groups and see aal comorbid conditions.

To avoid confusion for the enduser the selected conditions is highlighted and the title of the comorbid conditions is generated.

Mark