Set Analysis to Select/Filter by Values in Data

jcampbell — Mon, 14 Nov 2011 15:24:12 GMT

I just thought I would share something I did for a document I've been working on. I have a 'page' in the document that displays 12 graphs (bar charts) for different types of travel records (air, car, hotel, rail) and within each of those categories I have a graph that displays 'total paid', 'sum of records' and 'avg cost'.

The problem I had was that my data for air and rail is mixed together and only differentiated by a travel mode (A or R). I had to use Set Analysis to properly sum the total cost and number of records for air:

SUM({$<[Mode]={'R'}>}[Cost])

I used a similar expression for number of records. Then I encountered a problem where the avg cost per record was being divided (the cost for Rail) by all records (not just Rail). I eventually got it to work with the following expression:

SUM({$<[Mode]={'R'}>}[Cost]) / SUM({$<[Mode]={'R'}>}[Record])

topic Set Analysis to Select/Filter by Values in Data in QlikView

Set Analysis to Select/Filter by Values in Data