Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

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

Ignore dimension in set analysis while maintaining conditionals

I have a straight table with 3 dimensions (State, Hospital, Nursing Unit). These dimensions act as Russian Nesting Dolls. Nursing Units make up a Hospital and all Hospitals are included within States. Just like you would expect. I want an expression that gives the total top responses for the hospital and an expression for the nursing units.

State    Hospital    Nursing Unit    Hospital Top Responses    Nursing Unit Top Responses

AL            A                  1                               30                                           10

AL            A                  2                               30                                            5

AL            A                  3                               30                                           15

CA           B                  1                              100                                          99

CA           B                  2                              100                                           1


Here's the code that I'm using to generate the "Hospital Responses" column;

=(sum(aggr(count( {$<Answer = {1} ,NursingUnit=>} SurvID ),Hospital)))


Where "Answer = 1" gives me the Top responses instead of other responses and "Nursing Unit=" SHOULD, in theory, skip the NursingUnit dimension and just count the Hospital responses where answer = 1. What I'm actually getting is

State    Hospital    Nursing Unit    Hospital Top Responses    Nursing Unit Top Responses

AL            A                  1                               0                                             10

AL            A                  2                              30                                             5

AL            A                  3                               0                                             15

CA           B                  1                             100                                           99

CA           B                  2                               0                                               1

One and only one of the "Hospital Top Responses" returns its correct value. The rest of the rows associated with each hospital returns 0. I need every row to return their respective Hospital Top Response. The "Nursing Unit Top Responses" column is correct for all.

I hope that makes sense.

1 Solution

Accepted Solutions
Highlighted

Also, I think you might be looking for this

Count(TOTAL <State, Hospital> {<Answer = {1}, NursingUnit>} SurvID)

Or this

=Only({<NursingUnit=>}Aggr(NODISTINCT Count({$<Answer = {1}, NursingUnit=>} SurvID), Hospital))

View solution in original post

3 Replies
Highlighted

You need to ignore selection in NursingUnit for your outer Sum() also

=(Sum({<NursingUnit=>}Aggr(Count({$<Answer = {1}, NursingUnit=>} SurvID), Hospital)))

Highlighted

Also, I think you might be looking for this

Count(TOTAL <State, Hospital> {<Answer = {1}, NursingUnit>} SurvID)

Or this

=Only({<NursingUnit=>}Aggr(NODISTINCT Count({$<Answer = {1}, NursingUnit=>} SurvID), Hospital))

View solution in original post

Highlighted
Contributor
Contributor

That second one did it