topic Re: Aggr Argh!!!! in QlikView

Aggr Argh!!!!

dinicholls — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Aggr Argh!!!!

ali_hijazi — Thu, 01 Dec 2016 13:38:12 GMT

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

Re: Aggr Argh!!!!

rubenmarin — Thu, 01 Dec 2016 13:38:41 GMT

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

Re: Aggr Argh!!!!

dinicholls — Thu, 01 Dec 2016 13:44:28 GMT

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

rubenmarin — Thu, 01 Dec 2016 13:50:00 GMT

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

Re: Aggr Argh!!!!

dinicholls — Thu, 01 Dec 2016 14:03:54 GMT

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

Re: Aggr Argh!!!!

sunny_talwar — Thu, 01 Dec 2016 14:06:09 GMT

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)

Re: Aggr Argh!!!!

dinicholls — Thu, 01 Dec 2016 14:27:33 GMT

Sunny,

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

Re: Aggr Argh!!!!

sunny_talwar — Thu, 01 Dec 2016 14:37:33 GMT

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?

Re: Aggr Argh!!!!

dinicholls — Thu, 01 Dec 2016 14:40:27 GMT

Hi,

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

Thanks

Re: Aggr Argh!!!!

rubenmarin — Thu, 01 Dec 2016 15:18:30 GMT

Hi Diane, have you tried what I posted before?:

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

I supose this is a calculated dimension, isn't it?

Re: Aggr Argh!!!!

dinicholls — Thu, 01 Dec 2016 15:59:19 GMT

Hi,

I tried, didn't work.

Yes, its a calculated dimension.

Re: Aggr Argh!!!!

rubenmarin — Thu, 01 Dec 2016 17:37:28 GMT

Hi, then I have to join Sunny in the request of a sample.

Re: Aggr Argh!!!!

dinicholls — Fri, 02 Dec 2016 10:29:44 GMT

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