Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
dinicholls
Contributor 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

Tags (3)
1 Solution

Accepted Solutions
dinicholls
Contributor II

Re: Aggr Argh!!!!

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
Partner

Re: Aggr Argh!!!!

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

I can walk on water when it freezes

Re: Aggr Argh!!!!

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
Contributor II

Re: Aggr Argh!!!!

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

Re: Aggr Argh!!!!

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
Contributor II

Re: Aggr Argh!!!!

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

Re: Aggr Argh!!!!

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
Contributor II

Re: Aggr Argh!!!!

Sunny,

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

Re: Aggr Argh!!!!

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
Contributor II

Re: Aggr Argh!!!!

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