Qlik Community

Ask a Question

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
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
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

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

Specialist
Specialist

This seems to make the number too low.

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

Specialist II
Specialist II

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

Specialist
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
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!