Greetings fellow QlikView-ites
I have an issue trying to calculate the count of values which depend on only one of the two dimensions in a pivot table, whereby I want the value of the first dimension to be set to that of the second.
Currently I use the formula below in an expression, and get the result in the table below.
The numbers are exactly those that I want, but I want to transpose them, e.g. all values of target region A should be 29, B should be 76, etc.
=Aggr(NODISTINCT Count(DISTINCT IntervieweeID), Region)
| Target region |
| Interviewee region | A | B | C | D | E |
|---|
| A | 29 | 29 | 29 | 29 | 29 |
| B | 76 | 76 | 76 | 76 | 76 |
| C | 5 | 5 | 5 | 5 | 5 |
| D | 17 | 17 | 17 | 17 | 17 |
| E | 40 | 40 | 40 | 40 | 40 |
The data behind the table looks like this (not all rows and not all fields are shown - has to do with whether people recognise one another or not):
| IntervieweeID | Region | TargetID | TargetRegion | IsReciprocal |
|---|
| E01 | A | E02 | B | 1 |
| E01 | A | E03 | C | 1 |
| E01 | A | E04 | D | 0 |
| E02 | B | E03 | C | 1 |
The formula above is giving me the count of IntervieweeID grouped by Region, irrespective of TargetRegion.
I want to say "give me the count of IntervieweeID grouped by Region where Region = TargetRegion"
The ultimate aim is to be able to say what percentage of people in one region were recognised by people in another.
Each interviewee id may appear as a target id, but if noone recognises an interviewee, then their id will be missing.
That's why I need to use the interviewee id to get the total number of people.
I have reached an intermediate compromise by calculating the values in the load script, which suits my immediate purposes, but my long term solution should reflect any filtering that is done on other fields in my dataset.
I have fiddled around with set analyses and using TOTAL to try and force filter values - all to no avail. For example, the following gives me just the same result as above:
=Aggr(NODISTINCT Count({<Region = $("=TargetRegion")>}DISTINCT IntervieweeID), Region)
Probably a red herring anyway, as I assume my set analysis is trying to filter for values where the two fields are the same, and not setting one to the other as I am trying to do.
Any help on this would be greatly appreciated.
