Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
evansabres
Specialist
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
achettipalli
Creator
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)



View solution in original post

7 Replies
sunny_talwar

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)

achettipalli
Creator
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)



evansabres
Specialist
Specialist
Author

This seems to make the number too low.

sunny_talwar

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

lorenzoconforti
Specialist II
Specialist II

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

evansabres
Specialist
Specialist
Author

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]), '#,###.')

achettipalli
Creator
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!