topic Re: Set Analysis in QlikView

Set Analysis

evansabres — Fri, 14 Oct 2016 18:26:15 GMT

I am looking to create a chart to compare prices over time for several different expressions,

Time = transDATE (my dimension)

Fields I want to chart:

-capLEVEL (examples of records in this field are "200 Club Center" "100 Level III"

-transBASEPRICE (dollar value that is dependent upon the transDATE

I think my expression should be something along the lines of:

=transBASEPRICE({$<capLEVEL={"200 Club Center"})

However, i cannot seem to get this to work. Where is my syntax incorrect?

Re: Set Analysis

johnw — Fri, 14 Oct 2016 18:34:47 GMT

Maybe something like:

sum( {<capLEVEL={'200 Club Center'}>} transBASEPRICE)
only({<capLEVEL={'200 Club Center'}>} transBASEPRICE)
avg( {<capLEVEL={'200 Club Center'}>} transBASEPRICE)

I don't know what you want to do with the transBASEPRICE, so I can't give you an exact answer. I'm also unclear if you really only want them for 200 Club Center, or if that's not the real requirement at all.

Re: Set Analysis

evansabres — Fri, 14 Oct 2016 18:38:29 GMT

John -

Thanks for the response. What I would like the chart to show is the transBASEPRICE for 200 Club Center on the transDATE.

So, over time the chart would show the movement of the price based on the transDATE. Not sure if I want to sum the number, but hope that provides some clarity.

Re: Set Analysis

johnw — Fri, 14 Oct 2016 18:55:27 GMT

It sounds like you're saying that there will be only a single value for transBASEPRICE for a given transDATE and for 200 Club Center? Then you could use only() to indicate this, but any of the 3 expressions above should work.

Re: Set Analysis

evansabres — Fri, 14 Oct 2016 19:02:03 GMT

That can and cannot be true. This data set consists of transactions. So on any transDATE, there can multiple transactions (transBASEPRICE) for the 200 Club Center, so in that line of thinking, I would think that sum would not work because if there are two transactions for 200 Club Center, say for $100 each, then the data point would be $200 for that particular transDATE.

Would something like this work as well??

=Max( {<capLEVEL={'200 Club Center'}>} transBASEPRICE)

Re: Set Analysis

johnw — Fri, 14 Oct 2016 19:04:44 GMT

Yes, the set expression should work with any aggregation function. If you want the maximum transBASEPRICE, then the expression you gave looks correct to me.