Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
Check attached file
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
PFA file
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
Chek this Updated file
Thank you Sree Nivas. Your solution won't work if i have data of more than 2 months. Thanks anyway.
Try this
if(aggr(max(Sale),Area)>avg(Sale),max(Sale),0)
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
You're right. Thank you Jonathan Dienst.
I've known this approach. It's usefull. Thanks.