Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

jcampbell
Contributor

Set Analysis to Select/Filter by Values in Data

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

Community Browser