Set Analysis without aggregate (in textbox)

[#1]

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!

[simenkg · Accepted solution]

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])

[MK_QSL]

Use below code in Text Box

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

[#4]

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!

[#5]

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.

[MK_QSL]

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]))

[simenkg]

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

[#8]

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!