Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
Creator II
Creator II

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
Highlighted
Partner
Partner

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

I can walk on water when it freezes
Highlighted

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

Highlighted
Creator II
Creator II

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

Highlighted

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

Highlighted
Creator II
Creator II

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

Highlighted

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)

Highlighted
Creator II
Creator II

Sunny,

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

Highlighted

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?

Highlighted
Creator II
Creator II

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