Creating tables using Set Analysis

[Anonymous]

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

[sunny_talwar]

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)

[Anonymous]

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!

[sunny_talwar]

May be this:

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

[Anonymous]

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

1             1

2             1

2             1

3             0,5

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!

[sunny_talwar]

Should be just this

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

or

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

or

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

or

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

[Anonymous]

Worked! Tks so much!