Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
denvercoker
New 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.

Tags (1)
1 Solution

Accepted Solutions

Re: Ignore dimension in set analysis while maintaining conditionals

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

Re: Ignore dimension in set analysis while maintaining conditionals

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

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

Re: Ignore dimension in set analysis while maintaining conditionals

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

denvercoker
New Contributor

Re: Ignore dimension in set analysis while maintaining conditionals

That second one did it