# QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
cancel
Showing results for
Did you mean:
Specialist

## Set Analysis in an Expression

I am attempting to create a table that has a dimension of 'Event Date' and 10 expressions that are all similarly structured.

My end goal is to have a value that is the price per ticket paid, based on the total sale divided by the total quantity sold.

My initial expression is: =NUM(avg({\$<PRICESCALE={"200 Club End"}>}[TOTALSALE]), '\$#,###.') which is giving me the average price of the 'TOTALSALE' when the 'PRICESCALE' is 200 Club End. However, I need to introduce the following count(seatnumber) to divide by the initial expression to result in the average price paid.

1 Solution

Accepted Solutions
Creator

I guess, you need the avg Price / ticket paid for respective clubs like 200 Club etc.

if it's set analysis you'd like to go with then, similar to the above comment (but small change):

=Sum({<PRICESCALE={"200 Club End"}>}[TOTALSALE])/Count({<PRICESCALE={"200 Club End"}>}seatnumber)

7 Replies
MVP

Seems like you need this (not 100% sure and may be some sample data might help explain)...

=Sum({\$<PRICESCALE={"200 Club End"}>}[TOTALSALE])/Count(seatnumber)

Creator

I guess, you need the avg Price / ticket paid for respective clubs like 200 Club etc.

if it's set analysis you'd like to go with then, similar to the above comment (but small change):

=Sum({<PRICESCALE={"200 Club End"}>}[TOTALSALE])/Count({<PRICESCALE={"200 Club End"}>}seatnumber)

Specialist

This seems to make the number too low.

MVP

I have no idea what you are getting and what you want... may be it's time to elaborate

Specialist II

=Sum({\$<PRICESCALE={"200 Club End"}>}[TOTALSALE])/Count({\$<PRICESCALE={"200 Club End"}>}seatnumber)

Specialist

I think this may work, but still getting results outside of what I would expect. To test this, Can I perform a set expression with no aggregation function? For example, ={\$<PRICESCALE={"300 Level V"}>}[TOTALSALE]), '#,###.')

Creator

Not really! As far as I know, Set analysis can only be used with aggregation functions.

I'd approach this problem by

1. Unit testing of separate expressions like checking the individual expressions in a text box. eg. =Sum({\$<PRICESCALE={"200 Club End"}>}[TOTALSALE])

OR

2. Using those expressions in table charts to see if any missing values or incompatible conversion errors.

Good luck!