topic Re: set analysis problem in QlikView

set analysis problem

Tue, 04 Mar 2014 03:55:57 GMT

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.

Re: set analysis problem

amars — Tue, 04 Mar 2014 04:17:54 GMT

Check attached file

Re: set analysis problem

Tue, 04 Mar 2014 04:26:49 GMT

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

Re: set analysis problem

sreenivas — Tue, 04 Mar 2014 04:35:49 GMT

PFA file

Re: set analysis problem

Tue, 04 Mar 2014 05:00:45 GMT

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

Re: Re: set analysis problem

sreenivas — Tue, 04 Mar 2014 05:09:33 GMT

Chek this Updated file

Re: set analysis problem

Tue, 04 Mar 2014 05:29:21 GMT

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

Re: set analysis problem

er_mohit — Tue, 04 Mar 2014 05:35:51 GMT

Try this

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

Re: set analysis problem

jonathandienst — Tue, 04 Mar 2014 05:55:18 GMT

This is the expression you need:

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

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

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

HTH

Jonathan

Re: set analysis problem

Tue, 04 Mar 2014 08:49:27 GMT

You're right. Thank you Jonathan Dienst.

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