set analysis problem

[#1]

Hi all,

     I have a table  :

Month	Area	Sale
1	A	191
1	B	210
1	C	180
1	D	200
2	A	90
2	B	200
2	C	180
2	D	119

I want to create a chart like this :

Area	avg	sum	sum(Sale > Avg,Sale)
A	140.5	281	191
B	205	410	210
C	180	360	0
D	159.5	319	200

But i don't know how to calculate the third one: sum all the Sale which are larger than the Area's avg.

Pls help me. Find the qvw file from the attachment.

[amars]

Check attached file

[#3]

Dear amars,

     Maybe you misunderstand my purpose, for each Area, I want to sum all the Sale of the Area which are larger than the avg Sale of the corresponding Area. the final chart may like this:

Area	avg	sum	sum(Sale > Avg,Sale)
A	140.5	281	191
B	205	410	210
C	180	360	0
D	159.5	319	200

My English is not so good, i don't know whether it makes you understood.

Thanks,

zhou

[sreenivas]

PFA file

[#5]

Thank you Sree Nivas. Your suggestion works well for my test app. But my real app is much more complicated, I can't do as what you suggested.

Thanks,

zhou

[sreenivas]

Chek this Updated file

[#7]

Thank you Sree Nivas. Your solution won't work if i have data of more than 2 months. Thanks anyway.

[er_mohit]

Try this

if(aggr(max(Sale),Area)>avg(Sale),max(Sale),0)

[jonathandienst · Accepted solution]

Hi

This is the expression you need:

Sum(Aggr(If(Sale >= Avg(TOTAL <Area> Sale), Sale), Area, Month))

OR

Sum(Aggr(If(Sale > Avg(TOTAL <Area> Sale), Sale), Area, Month))

(Which one depends on how you want to handle Area C)

HTH

Jonathan

[#10]

You're right. Thank you Jonathan Dienst.

I've known this approach. It's usefull. Thanks.