Aggr Expression help.

[apthansh]

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.

[sunny_talwar]

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))

or

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))

[apthansh]

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‌

[sunny_talwar]

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

[apthansh]

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.

[sunny_talwar · Accepted solution]

Try this

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

or

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

[apthansh]

Awesome.You are my saving grace

[apthansh]

stalwar1‌

Hello there...

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

[sunny_talwar]

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