topic Re: Creating tables using Set Analysis in QlikView

Creating tables using Set Analysis

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi All!

Id like a help from you guys with set analysis commands. Id like to create a new table by using set analysis and then sum the values on one field.

I have now the following table:

Month	Product	Chanel	Consider?	Expectation
1	1	A	1	0,5
2	1	A	1	1
1	2	A	0	0,4
2	2	B	1	0,3
1	3	A	1	1
1	4	B	1	0,4

What I want to do is to create a new table from this one where the value on "Consider?" field is equal to 1 and where the "Product" field would contain only distinct products. After all, Id like to sum the values on the "Expectation" field.

So the new table would be like (the key would be "Month-Product"):

Month	Product	Expectation
1	1	0,5
2	2	0,4
1	3	1
1	4	0,4
2	1	1

In the end, id like to sum the expectation, then the final result would be 3,3.

Does anyone know how to do that?

Regards

Re: Creating tables using Set Analysis

sunny_talwar — Wed, 28 Dec 2016 14:59:29 GMT

I think Month 2 Product 2 has an expectation of 0,3 and not 0,4.

where the "Product" field would contain only distinct products.

I don't understand this part, but may be try this:

Sum({<[Consider?] = {1}>}Expectation)

Re: Creating tables using Set Analysis

Anonymous — Wed, 28 Dec 2016 15:42:53 GMT

Hey Sunny, tls for the answer!

But please note that the Chanel should not be taken into account for the calculation.

So, if we have the same product with more than one chanel in one month, we will only consider it once (it will always have the same expectation in that case).

Is it clearer now for you?

Tks!

Re: Creating tables using Set Analysis

sunny_talwar — Wed, 28 Dec 2016 15:44:52 GMT

May be this:

Sum({<[Consider?] = {1}>} Aggr(Sum(DISTINCT {<[Consider?] = {1}>}Expectation), Month, Product))

Re: Creating tables using Set Analysis

Anonymous — Wed, 28 Dec 2016 17:26:28 GMT

Hey Sunny, tks again, but still didnt work

Sorry to take so much of your time. I manipulated the database somehow to simplify it.

What I got now i that:

ID EXPECTATION

1 1

2 1

3 0,5

The outcome of this would be 2,5 (1 - 1/ 2 - 1 / 3 - 0,5)

Have you got any ideia of how i can make it?

Tks again!

Re: Creating tables using Set Analysis

sunny_talwar — Wed, 28 Dec 2016 17:31:28 GMT

Should be just this

Sum(Aggr(Sum(DISTINCT EXPECTATION), ID))

Sum(Aggr(Avg(EXPECTATION), ID))

Sum(Aggr(Min(EXPECTATION), ID))

Sum(Aggr(Max(EXPECTATION), ID))

Re: Creating tables using Set Analysis

Anonymous — Wed, 28 Dec 2016 17:50:04 GMT

Worked! Tks so much!