Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for
Did you mean:
Creator II

## Aggr Argh!!!!

Hi All,

I have the following calculated dimension:

=if(Aggr(Rank(sum({<[Group No] -= { 'GR-000000','GR-A0000' }>}[>60days])),[Group No])<=15,[Group No],Null())

I need to add in the following:

ME={'ME1610'}

I've tried this:

=if(Aggr(Rank(sum({<[Group No] -= { 'GR-000000','GR-A0000' },{\$<ME={'ME1610'}>}[>60days])),[Group No])<=15,[Group No],Null())

But it just bring back rubbish. Where an I going wrong?

Thank you

Di

1 Solution

Accepted Solutions
Creator II
Author

Hi All,

Thanks for trying to fix this, I actually so of 'fudged' it, and pulled the info I need via a resident load, pulling only data for ME = 'ME1610', and used the ranking formula, minus the ME bit.

Thanks for all your help!

If you ever come across something like this again, please tag me in it so I can have a look!!

Di

13 Replies
Partner - Master II

{\$<ME={'ME1610'}> is not a valid syntax

I can walk on water when it freezes
MVP

Hi Diane, the syntaxis should be:

=if(Aggr(Rank(sum({<[Group No] -= { 'GR-000000','GR-A0000' }, ME={'ME1610'}>}[>60days])),[Group No])<=15,[Group No],Null())

Creator II
Author

Hi Ruben,

Thats not worked. Just brings back the totals for all the ME's in the file, and not ME1610.

Add brackets to the 'ME', but still no luck

=if(Aggr(Rank(sum({<[Group No] -= { 'GR-000000','GR-A0000' }, [ME]={'ME1610'}>}[>60days])),[Group No])<=15,[Group No],Null())

Its driving me potty!!! Lol

MVP

Hi Diane, I just told the correct syntaxis, about the logic... what do you want to do?

From what I see you're checking rank from all the records included in aggr() so unless you have [Group No] as dimension or 1 [Group No] selected I don't think this is correct. And in that case the aggr will have no use.

IF() will return false if the condition is 0, True for any other number, so rank will always return a value<>0 (or null) and this is why every [Group No] is included in the True part. (More than this, the actual way is not evaluting group by group, is evaluating all of them as a unit)

Maybe you want (jut a guess...):

=Aggr(if(Rank(sum({<[Group No] -= { 'GR-000000','GR-A0000' }, [ME]={'ME1610'}>}[>60days]))<=15,[Group No]),[Group No])

Creator II
Author

Hi Ruben,

I'm trying to rank the top 15 group numbers by >60Days debt, but exclude group numbers 'GR-000000', and 'GR-A0000', and only for the ME = ME1610.

Does that make sense?!

Thanks

Di

MVP

May be you need to add the set analysis to your expression. Assuming you have Sum(Measure), change it to this

Sum({<ME = {'ME1610'}>} Measure)

Creator II
Author

Sunny,

How will that rank the group number by >60 day debt?

MVP

I am not 100% sure what you input and output look like. It was just a guess. Would you be able to provide a sample with expected output?

Creator II
Author

Hi,

Not allowed to, but I'll see if I can put something together so you can see what I'm aiming for.

Thanks

Di

Community Browser