Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

mbanerjee95
New Contributor

How can I add a drill-down dimension to this visualization?

Hello all,

I'm currently working on a Qlik Sense Desktop visualization regarding cancer statistics. In my sheet, I am looking at the ten highest states regarding the risks of overall cancer and added their respective cancer rates. However, I would like to drill-down into those state rates and reveal the rates for individual cancer rates (ex. liver, breast, and kidney cancer) although I'm not sure how this can be accomplished.  I'm not sure if this is something done in Qlik Sense Desktop itself or how the Excel sheet is formatted. I am adding screenshots below to provide more insight regarding my problem. I have been stuck for quite some time now and any help would be much appreciated. Thanks again in advance!

cancer.PNGcancer2.PNG

1 Solution

Accepted Solutions
mov
Esteemed Contributor III

Re: How can I add a drill-down dimension to this visualization?

First thing you need to do is to use crosstable load to get a cancer type as a field, not as a column:

Crosstable (CancerType,Rate)

LOAD

State,

KidneyRate as Kidney,

LingRate as Ling,

BreastRate as Breast,

LiverRate as Liver

FROM ...

You'll get a table with the Fields State, CancerType, Rate.

Next, create a master dimension, drill-down,including State and CancerType.  Use this dimension in your charts.

8 Replies
achakilam1022
Contributor

Re: How can I add a drill-down dimension to this visualization?

Hi Debashish!

I think if you want to use a drilled down dimension of state and cancer type then your data must be something like the following:

State, CancerType, Cancer%

AZ, Lung, 10%

AZ, Kidney, 20%

AZ, breast, 20%

AZ, liver, 2%

......

.......

Then you can create a drilled down dimension of State and CancerType. And add this new drilldown as dimension in bar graph and Cancer% as measure.

I hope I got your question correctly.

Thanks,

Amuktha

mov
Esteemed Contributor III

Re: How can I add a drill-down dimension to this visualization?

First thing you need to do is to use crosstable load to get a cancer type as a field, not as a column:

Crosstable (CancerType,Rate)

LOAD

State,

KidneyRate as Kidney,

LingRate as Ling,

BreastRate as Breast,

LiverRate as Liver

FROM ...

You'll get a table with the Fields State, CancerType, Rate.

Next, create a master dimension, drill-down,including State and CancerType.  Use this dimension in your charts.

mbanerjee95
New Contributor

Re: How can I add a drill-down dimension to this visualization?

Thank you Michael and Amuktha for your suggestions. I used the crosstable load as you've stated and it successfully created the CancerType bars after you drill down into the State. However, all the bars are the same for each state while some do not even appear under CancerType such as Missouri (screenshot below). What could be the reason for this as well as the fix? Thanks again!cancer3.PNGcancer4.PNG

mov
Esteemed Contributor III

Re: How can I add a drill-down dimension to this visualization?

To help further, need to know the data and the expressions.  Maybe you can upload your app so we can see what's there.

mbanerjee95
New Contributor

Re: How can I add a drill-down dimension to this visualization?

Here's the QVF to my application

mov
Esteemed Contributor III

Re: How can I add a drill-down dimension to this visualization?

It helps.

The expressions on the front end must be sum(Rate), not the sum(Rate2).  The field Rate is the one which you get from the CancerData file.

Some states, like Missouri, simply don't have anything in this table.

mbanerjee95
New Contributor

Re: How can I add a drill-down dimension to this visualization?

Alright, I understand. However, I added new rates to another Excel sheet so I'm incorporating "CancerRate" instead. How can I make it so that it displays these new rates for the individual types of cancer? How can I link the "CancerRate" to CancerData?

UPDATE: I tried changed Rate in the cross table load to Cancer Rate, however, it drastically alters the statistics for the initial visualization.

mov
Esteemed Contributor III

Re: How can I add a drill-down dimension to this visualization?

Are you replacing the old sheet with the new one,is it an addition?

In any case - it depends on the structure of the new sheet.