4 Replies Latest reply: Nov 27, 2014 3:01 AM by William Rogan RSS

    Aggregation in pivot table - set one dimension value to that of another

      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 regionABCDE
      A2929292929
      B7676767676
      C55555
      D1717171717
      E4040404040

       

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

       

      IntervieweeIDRegionTargetIDTargetRegionIsReciprocal
      E01AE02B1
      E01AE03C1
      E01AE04D0
      E02BE03

      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.