topic Re: Set Analysis Sum question in QlikView

Set Analysis Sum question

Mon, 06 Oct 2014 14:36:44 GMT

Hi, I'm having trouble getting my set analysis query to work for a certain variable.

What I'm trying to do is show how many sales we've made at a loss and how many at a profit. This has been relatively easy for volume, sales and margin as I've just used the expression:

=Sum({$<Gross_Margin={"<$(=0)"}>} transaction_quantity)

(or some other version of the formula for profit making products)

However I'm having trouble when I'm looking at SKU count. If I use the same formula as above for SKU count:

=count({$<Gross_Margin ={"<$(=0)"}>} DISTINCT Teva_SKU_Current)

For loss making products and:

=count({$<Gross_Margin ={">=$(=0)"}>} DISTINCT Teva_SKU_Current)

for profit making products then the graph seems to double count products if one customer purchased them at a profit and another at a loss. I think I somehow need to get the formula to look at the total Gross_Margin figure rather than at an individual basis but I'm not sure how to do that.

Any ideas?

Re: Set Analysis Sum question

JonnyPoole — Mon, 06 Oct 2014 15:07:30 GMT

You may need an advanced aggregation expression, before guessing can you supply a sample QVW or some data w/ explanation ?

Re: Set Analysis Sum question

Mon, 06 Oct 2014 20:58:20 GMT

Hello there,

without seeing a sample I am just guessing here, but in your formula you have Count if gross margin =0, and your second formula Count if Greater than or equal. My guess is '0' is being double counted on both formulas.

Re: Set Analysis Sum question

Anonymous — Mon, 06 Oct 2014 21:16:07 GMT

create separate fields as flags for each condition and then count the values

and again as mentioned by others .. if you provide some sample data/ sample qvw, it would be easy for us to help you

Thanks

Sri1

Re: Set Analysis Sum question

Anonymous — Mon, 06 Oct 2014 22:33:28 GMT

Hi,

Could you try the following (and if not working, please provide an example QVW):

=count({<Teva_SKU_Current = {"=Sum(Gross_Margin)<=0"}>} DISTINCT Teva_SKU_Current)

Re: Set Analysis Sum question

Wed, 08 Oct 2014 15:03:39 GMT

OK, so I've attached an example csv of the type of data that I mean. If you look there are three distinct SKU_Current codes (a, b and c) but because the c code is both profitable and making a loss (due to being priced differently depending on the customer) then when I use the following expressions then it is counted as a profitable and a loss-making product when really it should be loss making (5 - 10 = -5).

At the moment I am using the following expressions:

=count({$<Gross_Margin ={"<$(=0)"}>} DISTINCT SKU_Current)

and

=count({$<Gross_Margin ={"<=$(=0)"}>} DISTINCT SKU_Current)

SKU_Current	transation_quantity	Gross_Sales	Gross_Margin
a	4	10	2
b	6	2	-2
c	10	15	5
c	3	3	-10