topic Re: Possible Set Analysis Question in QlikView

Possible Set Analysis Question

jcampbell474 — Sun, 19 Apr 2015 05:06:19 GMT

I'm trying to write an expression that looks at the # of returns and the # of sales for each part number, then calculate the sum only if the difference is >0. I also have a column indicating if it is a return or a sale.

I can get it to work when the part number is a dimension by using:

if(Sum({< [CR] = {'CR'} >} [Qty])-Sum({< [CR] = {'Sales'} >} [Qty])>0,Sum({< [CR] = {'CR'} >} [Qty])-Sum({< [CR] = {'Sales'} >} [Qty]))

(CR is the column that identifies if the record/row is a [CR]Return or [Sales]Sale.)

Example: If part xxx had 5 returns, 10 sales, the difference would be -5. I need this to be omitted from the overall total.

If part yyy had 10 returns, 5 sales, then the difference would be 5. I need this to be included in the overall sum.

I can't get an overall sum of the records that are >0 without including the part number as a dimension. It gives me a sum, but adds all of the returns and sales, then subtracts the sales from returns.

I've worked with this for quite some time, inserting [Part number] hoping it would perform the calculation for each row. It simply sums both categories (CR and Sales) and gives me the difference.

=if(Sum({< [Part Number] = ,[CR] = {'CR'} >} [Qty])-Sum({< [Part Number] = ,[CR] = {'Sales'} >} [Qty])>0,Sum({< [CR] = {'CR'} >} [Qty])-Sum({< [CR] = {'Sales'} >} [Qty]))

How do I get an expression to work with each record/row without using a dimension, then include only the records/rows that meet the criteria in another expression? What am I missing or doing wrong?

Re: Possible Set Analysis Question

maxgro — Sun, 19 Apr 2015 06:12:38 GMT

it seems to me you could solve with the aggr function

sum(aggr(yourexpression, [Part Number]))

https://community.qlikview.com/docs/DOC-3857

Re: Possible Set Analysis Question

jcampbell474 — Sun, 19 Apr 2015 13:11:00 GMT

Thank you, Massimo. It feels like this is the solution. At the same time, I cannot get it to work. Does the syntax look correct?

sum(aggr(Sum({< [Part Number] = ,[CR] = {'CR'} >} [Qty])-Sum({< [Part Number] = ,[CR] = {'Sales'} >} [Qty])>0,Sum({< [CR] = {'CR'} >} [Qty])-Sum({< [CR] = {'Sales'} >} [Qty]), [Part Number])) does not provide a sum.

sum(aggr(Sum({< [Part Number] = ,[CR] = {'CR'} >} [Qty])-Sum({< [Part Number] = ,[CR] = {'Sales'} >} [Qty])>0, [Part Number])) gives me a sum, but it is much lower than what it should be.