topic Re: Need help in aggr function in QlikView

Need help in aggr function

sunil2288 — Fri, 30 Mar 2012 07:33:17 GMT

Hi All,

Actually i have a problem in straight table chart.

In this app when i enter the variable as 7 , it is showing the top 6 sum(sales), but in the total row it is showing the total of all the rows (86000) instead of 66000.

I have attched the application, as it is having inline data all can reload this app.

I have also a second requirement i need the % Contribution of Top N "Sum(Sales)" against the Sum(Total sales).

I have tried the Set expression but it is not working

sum({$<Dept={"aggr(if(rank(sum(Sales))<=$(V1),Dept),Name,Dept,ID)"}>}Sales) / Sum(TOTAL Sales)

here i need the %Contribution of Sum(Sales) of Top V1 Dept against Sum(Total Sales)

Please help me solving this and let me know if any queries regarding understanding the requirement.

PFA application.

Regards

Sunil

Re: Need help in aggr function

perumal_41 — Fri, 30 Mar 2012 08:34:00 GMT

Hi Sunil,

I attache d one file .please check is correct or not .

i thing useful for u.

Regards

Perumal A

Re: Need help in aggr function

Fri, 30 Mar 2012 09:41:53 GMT

Hi Perumal,

Great Work this what it should be

nice work !!!!!

Warm Regards

Anant

Need help in aggr function

sunil2288 — Fri, 30 Mar 2012 13:20:18 GMT

Hi Perumal,

"Sum(Aggr(if(rank(sum(Sales))<=V1,sum(Sales)),ID))"

Thanks for your reply. Actually here we have a field which is Unique and not null (i.e. ID). But in real scenario what i am facing is : ID is not unique. Rather the combination of all the fields make a row unique.

The Data is like:

LOAD * INLINE [

Name, Dept, ID, Sales

A, aa, 1001, 5000

B, bb, 1002, 2000

C, cc, 1003, 3000

D, dd, 1004, 4000

E, aa, 1005, 1000

F, bb, 1006, 5000

G, cc, 1007, 6000

H, dd, 1009, 10000

I, aa, 1009, 14000

J, bb, 1010, 12000

K, cc, 1010, 9000

L, dd, 1012, 8000

M, aa, 1013, 7000

];

i hope u will be able to understand my problem.

Regards

Sunil

Re: Need help in aggr function

sunil2288 — Fri, 30 Mar 2012 13:26:36 GMT

Hi Perumal,

"Sum(Aggr(if(rank(sum(Sales))<=V1,sum(Sales)),ID))"

The Data is like:

LOAD * INLINE [

Name, Dept, ID, Sales

A, aa, 1001, 5000

B, bb, 1002, 2000

C, cc, 1003, 3000

D, dd, 1004, 4000

E, aa, 1005, 1000

F, bb, 1006, 5000

G, cc, 1007, 6000

H, dd, 1009, 10000

I, aa, 1009, 14000

J, bb, 1010, 12000

K, cc, 1010, 9000

L, dd, 1012, 8000

M, aa, 1013, 7000

];

i hope u will be able to understand my problem.

Regards

Sunil

Re: Need help in aggr function

perumal_41 — Fri, 30 Mar 2012 13:48:22 GMT

hi Sunil

try this expression

Sum(Aggr(if(rank(sum(Sales))<=V1,sum(Sales)),ID&Name&Dept))

Regards

Perumal

Need help in aggr function

jagan — Fri, 30 Mar 2012 14:04:38 GMT

Hi,

Use this expression if there are no Unique ID fields

Sum(Aggr(if(rank(sum(Sales))<=V1,sum(Sales)),ID, Name))

Regards,

Jagan.

Re: Need help in aggr function

sunil2288 — Mon, 02 Apr 2012 04:36:47 GMT

Hi Perumal,

While i use this expression

Sum(Aggr(if(rank(sum(Sales))<=V1,sum(Sales)),ID&Name&Dept))

It shows wrong value.

Here though H,bb,1010 having sum(Sales) as 12000 but in the chart it is showing L,dd,1012 having sum(sales) 12000 , which is wrong information.

Qlikview file Attached .

Still i am not able to get the correct value.

Regards

Re: Need help in aggr function

CELAMBARASAN — Mon, 02 Apr 2012 04:49:11 GMT

Hi,

Try with this expression

=Sum({<ID={"=Rank(Sum(Sales))<$(V1)"}>}Sales)

Celambarasan

Re: Need help in aggr function

CELAMBARASAN — Mon, 02 Apr 2012 05:11:09 GMT

Hi,

Change you table structure like this with unique field

Load Name&Dept&ID as Key,*;

LOAD * INLINE [

Name, Dept, ID, Sales

A, aa, 1001, 5000

B, bb, 1002, 2000

C, cc, 1003, 3000

D, dd, 1004, 4000

E, aa, 1005, 1000

F, bb, 1006, 5000

G, cc, 1007, 6000

H, dd, 1009, 10000

I, aa, 1009, 14000

J, bb, 1010, 12000

K, cc, 1010, 9000

L, dd, 1012, 8000

M, aa, 1013, 7000

];

Use following one as expression

=Sum({<Key={"=Rank(Sum(Sales))<=$(V1)"}>} Sales)

Re: Need help in aggr function

sunil2288 — Mon, 02 Apr 2012 05:31:55 GMT

Hi Celam,

Thanks for your valuable effort,

I tried with the expression you have suggested :

Sum({<ID={"=Rank(Sum(Sales))<4"}>}Sales)

but with this expression the total value showing wrong.

Screenshot attached .

anyways thanks for your effort.

Regards

Sunil

Re: Need help in aggr function

sunil2288 — Mon, 02 Apr 2012 05:32:33 GMT

Hi Celam,

Thanks for your valuable effort,

I tried with the expression you have suggested :

Sum({<ID={"=Rank(Sum(Sales))<4"}>}Sales)

but with this expression the total value showing wrong.

Screenshot attached .

anyways thanks for your effort.

Regards

Sunil

Re: Need help in aggr function

CELAMBARASAN — Mon, 02 Apr 2012 05:47:46 GMT

Hi,

Did you checked with the another solution?

Celambarasan

Re: Need help in aggr function

sunil2288 — Mon, 02 Apr 2012 05:57:13 GMT

Hi Celam,

You are absolutely right if i will add a Key, all the problem will get resolved, but in the real case scenario what i am facing there is no scope of changing the Data Model, as becoz this expression has to be implemented on 8 dimensions which also includes Calculated dimensions. and the data model can't be changed.

So it has to be get resolved in the frontend.

Thanks for your quick reply..

Thanks

Sunil

Re: Need help in aggr function

CELAMBARASAN — Mon, 02 Apr 2012 06:03:13 GMT

Hi,

Changing data model is possible.Having a unique field in a table will be a good practice.Can you post the script how you created the table?.let me try to change it

Celambarasan

Re: Need help in aggr function

sunil2288 — Mon, 02 Apr 2012 06:25:02 GMT

Hi Celam,

I agree it is good practice to have Unique field in a table. In this case the dimensions are from different table and expressions are from different table (Not a single expression too many expression has to be implemented like this). Sorry to say but again i am repeating there is no scope of changing the data model and reload again.

Anyway i need to do in the frontend.

Thanks for your reply.

Regards
Sunil

Need help in aggr function

perumal_41 — Mon, 02 Apr 2012 06:26:35 GMT

Hi Sunil ,

Try this expression

Sum(Total <ID,Name,Dept> Aggr(Distinct if(rank(sum(Sales))<=V1,sum(Sales)),ID))

Regards

Perumal A

Need help in aggr function

CELAMBARASAN — Mon, 02 Apr 2012 06:41:40 GMT

Hi,

Then try this expression

=Sum(aggr(if(Rank(Total Sum(Sales))<=V1,Sum(Sales)),Name,ID,Dept))

Celambarasan

Re: Need help in aggr function

sunil2288 — Mon, 02 Apr 2012 14:17:37 GMT

Hi Celam and Perumal,

Thanks for your response. The expression given by Celam is working correctly. How much i can able to do it in my complicated case has to be watched.

Sum(aggr(if(Rank(Total Sum(Sales))<=V1,Sum(Sales)),Name,ID,Dept))

Thanks

Sunil.