Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Set Analysis without aggregate (in textbox)

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!

1 Solution

Accepted Solutions
Highlighted
Partner
Partner

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


View solution in original post

7 Replies
Highlighted
Partner
Partner

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


View solution in original post

Highlighted
MVP
MVP

Use below code in Text Box

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

Highlighted
Not applicable

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!

Highlighted
Not applicable

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.

Highlighted
MVP
MVP

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

Highlighted
Partner
Partner

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

Highlighted
Not applicable

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!