Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
dinicholls
Creator II
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
dinicholls
Creator II
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

View solution in original post

13 Replies
ali_hijazi
Partner - Master II
Partner - Master II

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

I can walk on water when it freezes
rubenmarin

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

dinicholls
Creator II
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

rubenmarin

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

dinicholls
Creator II
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

sunny_talwar

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)

dinicholls
Creator II
Creator II
Author

Sunny,

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

sunny_talwar

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?

dinicholls
Creator II
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