topic Set Analysis in an Expression in QlikView

Set Analysis in an Expression

evansabres — Thu, 18 Oct 2018 14:54:54 GMT

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.

Re: Set Analysis in an Expression

sunny_talwar — Thu, 18 Oct 2018 14:57:52 GMT

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)

Re: Set Analysis in an Expression

achettipalli — Thu, 18 Oct 2018 15:10:41 GMT

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)

Re: Set Analysis in an Expression

evansabres — Thu, 18 Oct 2018 15:21:06 GMT

This seems to make the number too low.

Re: Set Analysis in an Expression

sunny_talwar — Thu, 18 Oct 2018 15:26:16 GMT

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

Re: Set Analysis in an Expression

lorenzoconforti — Thu, 18 Oct 2018 15:34:25 GMT

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

Re: Set Analysis in an Expression

evansabres — Thu, 18 Oct 2018 18:15:05 GMT

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

Re: Set Analysis in an Expression

achettipalli — Thu, 18 Oct 2018 20:35:24 GMT

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

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

Good luck!