topic Re: Aggr Expression help. in QlikView

Aggr Expression help.

apthansh — Wed, 25 Nov 2020 16:16:04 GMT

I have the below exoression in bar chart and my dimension is year & Qtr.

I need to get the sum on Distinct Code.How can I do that ?

Sum(Aggr(sum(if([Type]='Direct Lease',SQF_Committed)) <=0,

sum(if([Type]='Direct Lease',SQF_Committed_Planned),

sum(if([Type]='Direct Lease',SQF_Committed))),CodeKey))

I tried the bwlow exoression,but it dosnt work.

Sum(Aggr(sum(if([Type]='Direct Lease',SQF_Committed)) <=0,

sum(if([Type]='Direct Lease',SQF_Committed_Planned),

sum(if([Type]='Direct Lease',SQF_Committed))),distinct CodeKey))

Thanks much.

Re: Aggr Expression help.

sunny_talwar — Tue, 13 Jun 2017 13:22:57 GMT

Not sure what you trying to do, but are you missing an if statement?

Sum(Aggr(

If(Sum({<Type = {'Direct Lease'}>} SQF_Committed) <= 0,

Sum({<Type = {'Direct Lease'}>} SQF_Committed_Planned),

Sum({<Type = {'Direct Lease'}>} SQF_Committed))

, CodeKey))

Sum(Aggr(

If(Sum({<Type = {'Direct Lease'}>} SQF_Committed) <= 0,

Sum({<Type = {'Direct Lease'}>} SQF_Committed_Planned),

Sum({<Type = {'Direct Lease'}>} SQF_Committed))

, CodeKey, Year, Qtr))

Re: Aggr Expression help.

apthansh — Tue, 13 Jun 2017 13:31:35 GMT

Thank you Sunny.

Sum(Aggr(sum(if([Type]='Direct Lease',SQF_Committed)) <=0,

sum(if([Type]='Direct Lease',SQF_Committed_Planned),

sum(if([Type]='Direct Lease',SQF_Committed))),CodeKey))

This expression works fine but when I have same CodeKey twice its double counting the SQF.

I want to get the sum of SQF on distinct CodeKey.Can I do that ?

@sunnyt stalwar1‌

Re: Aggr Expression help.

sunny_talwar — Tue, 13 Jun 2017 13:33:14 GMT

Would you be able to share a sample to show the issue?

Re: Aggr Expression help.

apthansh — Tue, 13 Jun 2017 13:49:47 GMT

sunny‌stalwar1‌

PFA sample file and QVW.

I need to get the sum of SF on distinct Code instead of 1000 it should return 500.Is it doable ?

Thanks much.

Re: Aggr Expression help.

sunny_talwar — Tue, 13 Jun 2017 14:00:01 GMT

Try this

=Sum({<[Type] = {'Direct Lease'}>}Aggr (Avg({<[Type] = {'Direct Lease'}>} SF), Code))

=Sum(Aggr (Avg({<[Type] = {'Direct Lease'}>} SF), Code))

Re: Aggr Expression help.

apthansh — Thu, 15 Jun 2017 12:24:26 GMT

Awesome.You are my saving grace

Re: Aggr Expression help.

apthansh — Fri, 16 Jun 2017 18:49:33 GMT

stalwar1‌

Hello there...

can I put Max on this expression to get the Maximun SF among all the code.

Re: Aggr Expression help.

sunny_talwar — Fri, 16 Jun 2017 19:00:58 GMT

I believe so... have you tried it isn't working?