topic Re: Set Analysis without aggregate (in textbox) in QlikView

Set Analysis without aggregate (in textbox)

Tue, 02 Dec 2014 20:50:50 GMT

I am trying to display certain values from a data set in a text box. The problem I am having is that I think I require set analysis to achieve this, but I cannot use an aggregate as it will not create the desired output.

I have multiple textboxes on a splash screen in an application. Each textbox needs to display a value that is found in a row from the corresponding data set.

Here is an example of the data set:

Employee ID Hospital Code Service Line Employee Amount Hospital Amount

1 A Lung Cancer 10 75

2 A Lung Cancer 20 75

2 A Pancreatic Cancer 30 80

3 A Pancreatic Cancer 40 80

4 A Prostate Cancer 50 100

5 A Skin Cancer 60 150

Here is an example of the desired output on the front end. What I am looking to display is the Hospital Amount listed for each Service Line irrelevant of the Employee Amount and not aggregated together. Each line below (Lung, Pancreatic, etc) represents a text box with a value.

Oncology Breakdown

-----------------------------------

Lung - 75

Pancreatic - 80

Prostate - 100

Skin - 150

I realize I can do this with a straight table or a table box, however aesthetically these are not what my clients are looking for. As such I am utilizing mostly text boxes for my visuals, including the data values as well as various non-QlikView images.

I would be looking for something like this (I removed the usual aggregate):

{$< [Service Line] = {'Lung Cancer'} >}[Hospital Amount]

From a data perspective I am trying to avoid having to create look up tables for each Service Line if possible as they number in the hundreds.

Any help or thoughts are appreciated. Thanks in advance!

Re: Set Analysis without aggregate (in textbox)

simenkg — Tue, 02 Dec 2014 21:07:35 GMT

This depends whether Hospital Amount can have multiple values for a given Service Line.

If only ONE value exists for each Hospital Amount then you can use either:

only{$< [Service Line] = {'Lung Cancer'} >}[Hospital Amount])
or

avg({$< [Service Line] = {'Lung Cancer'} >}[Hospital Amount])

If multiple values exists and you want to show the biggest:

Max({$< [Service Line] = {'Lung Cancer'} >}[Hospital Amount])

Smallest:

Min({$< [Service Line] = {'Lung Cancer'} >}[Hospital Amount])

Average:

Avg({$< [Service Line] = {'Lung Cancer'} >}[Hospital Amount])

Re: Set Analysis without aggregate (in textbox)

MK_QSL — Tue, 02 Dec 2014 23:29:11 GMT

Use below code in Text Box

=Concat(DISTINCT Aggr(Left([Service Line], Index([Service Line],'Cancer')-2) & '-' & Only([Hospital Amount]),[Service Line]), CHR(10))

Re: Set Analysis without aggregate (in textbox)

Wed, 03 Dec 2014 01:39:10 GMT

Thanks Simen. Unfortunately, I will have multiple values for a single service line, as there will be multiple Hospital Codes. For example, Hospital A can have a value for Lung Cancer and Hospital B can have a value for Lung Cancer.

Do you have any options for displaying the exact amount?

I do appreciate the input so far. Thank you!

Re: Set Analysis without aggregate (in textbox)

Wed, 03 Dec 2014 02:09:27 GMT

Manish, I tried your code but was unable to get the desired result.

To clarify, have a separate text box for the text (example: 'Lung Cancer') and a separate text box for the values. I tried to modify your code like below but was unable to get a result.

=Aggr([Service Line] & '-' & Only([Hospital Amount]),[Service Line])

Please let me know if you have any idea how to derive the value only.

I appreciate your help.

Re: Re: Set Analysis without aggregate (in textbox)

MK_QSL — Wed, 03 Dec 2014 07:56:35 GMT

The code I have provided was for a single text box...

If you want Name and Values to be represented in different Text Box, use below

=Concat(DISTINCT Left([Service Line], Index([Service Line],'Cancer')-2), CHR(10),Aggr(SUM(DISTINCT[Hospital Amount]),[Service Line]))

=Concat(Aggr(SUM(DISTINCT [Hospital Amount]),[Service Line]),CHR(10),Aggr(SUM(DISTINCT [Hospital Amount]),[Service Line]))

Re: Set Analysis without aggregate (in textbox)

simenkg — Wed, 03 Dec 2014 08:23:39 GMT

In your example there is only one Hospital Code.

Please specify what your expected output is for a Service Line over multiple Hospitals?

In the example under. What would your text box contain?

1 A Lung Cancer 10 75

2 A Lung Cancer 20 75

3 B Lung Cancer 30 100

Re: Set Analysis without aggregate (in textbox)

Wed, 03 Dec 2014 16:36:33 GMT

My apologies Simen as your first answer was correct. We will have a client select a single Hospital Code first and then select a single Service Line to display the data. The 'only' function with set analysis worked perfectly.

Thanks so much for your help!