topic Re: Using AGGR in PICK MATCH issue in QlikView

Using AGGR in PICK MATCH issue

rustyfishbones — Fri, 27 Jan 2017 21:27:29 GMT

Hi All,

I have the following expression

PICK(

MATCH(

VALUELIST('Ranked','Picked','% Overall'),

'Ranked','Picked','% Overall'),

AGGR(RANK(SUM({<[TrxType] = {'D'},ActionID = {'PICKCASE'},OperativeName=>}$(metric2))),OperativeName),

NUM(SUM({< [TrxType] = {'D'},ActionID = {'PICKCASE'}>}$(metric2)),'#,##0'),

NUM(SUM(TOTAL {<[TrxType] = {'D'},ActionID = {'PICKCASE'},OperativeName=>}$(metric2)),'#,##0'))

It works fine,

However when I want to add 'st', 'nd', 'rd' to the expression it fails

So if the Rank value returns 1, I want to display 1st.

I tried with the following and it works for 1st

PICK(

MATCH(

VALUELIST('Ranked','Picked','% Overall'),

'Ranked','Picked','% Overall'),

AGGR(RANK(SUM({<[TrxType] = {'D'},ActionID = {'PICKCASE'},OperativeName=>}$(metric2))),OperativeName) &

IF(RIGHT(AGGR(RANK(SUM({<[TrxType] = {'D'},ActionID = {'PICKCASE'},OperativeName=>}$(metric2))),OperativeName),1) = 1,'st','th'),

NUM(SUM({< [TrxType] = {'D'},ActionID = {'PICKCASE'}>}$(metric2)),'#,##0'),

NUM(SUM(TOTAL {<[TrxType] = {'D'},ActionID = {'PICKCASE'},OperativeName=>}$(metric2)),'#,##0'))

The problem is when I try to add '2nd', '3rd'

It does not like the Nested IF Statement within the PICK and MATCH

Any Ideas?

Thanks

Alan

Re: Using AGGR in PICK MATCH issue

swuehl — Fri, 27 Jan 2017 21:34:13 GMT

On a quick glance, I think your issue is very similar to

A Lot of AGGR Functions in a Quartile Chart

Re: Using AGGR in PICK MATCH issue

Oleg_Troyansky — Fri, 27 Jan 2017 21:43:15 GMT

In addition, may I suggest that you read this blog post of mine:

http://www.naturalsynergies.com/q-tip-14-aggr-and-synthetic-dimensions/

This is just one of the "strange" AGGR() behaviors that I'm explaining in detail at the Masters Summit for Qlik. We will be in Munich this April - come and join us!

cheers,

Oleg Troyansky

Re: Using AGGR in PICK MATCH issue

rustyfishbones — Fri, 27 Jan 2017 22:01:25 GMT

Hi Guys,

I remember seeing this post by Oleg Troyansky on QlikView Consulting, Training & Q-Tips | Natural Synergies

some time ago, however, I tried the said example and it made no difference.

I will keep trying, or attend some training! thanks Oleg

Regards

Alan

Re: Using AGGR in PICK MATCH issue

Oleg_Troyansky — Mon, 30 Jan 2017 00:31:00 GMT

Hi Alan,

if you'd like to post a sample, I wouldn't mind to take a look...

cheers,

Oleg Troyansky

Re: Using AGGR in PICK MATCH issue

rustyfishbones — Thu, 09 Feb 2017 22:36:03 GMT

Hi Oleg,

It's difficult to share the data, I will try to re-create the issue and provide a file you can test with

Thanks for the help

Regards

Alan

Re: Using AGGR in PICK MATCH issue

rustyfishbones — Thu, 09 Feb 2017 23:38:37 GMT

Hi Oleg,

PICK(

MATCH(

VALUELIST('Ranked','Picked','% Overall'),

'Ranked','Picked','% Overall'),

//RANKED

AGGR(RANK(SUM({<[TrxType] = {'D'},ActionID = {'PICKCASE'},OperativeName=>}$(metric2))),OperativeName) &

IF(RIGHT(AGGR(RANK(SUM({<[TrxType] = {'D'},ActionID = {'PICKCASE'},OperativeName=>}$(metric2))),OperativeName),1) = 1,'st','th'),

//PICKED

NUM(SUM({< [TrxType] = {'D'},ActionID = {'PICKCASE'}>}$(metric2)),'#,##0'),

//% OVERALL

NUM((SUM({1}{<[TrxType] = {'D'},ActionID = {'PICKCASE'}>}$(metric2))

SUM({<[TrxType] = {'D'},ActionID = {'PICKCASE'},OperativeName=>}$(metric2))),'0.00%'))

This shows the following result,

It's all correct, However It should be 2nd NOT 2th

Hope you can help

Re: Using AGGR in PICK MATCH issue

rustyfishbones — Thu, 09 Feb 2017 23:48:39 GMT

I got it

PICK(

MATCH(

VALUELIST('Ranked','Picked','% Overall'),

'Ranked','Picked','% Overall'),

//RANKED

AGGR(RANK(SUM({<[TrxType] = {'D'},ActionID = {'PICKCASE'},OperativeName=>}$(metric2))),OperativeName) &

IF(RIGHT(AGGR(RANK(SUM({<[TrxType] = {'D'},ActionID = {'PICKCASE'},OperativeName=>}$(metric2))),OperativeName),1) = 1,'st',

IF(RIGHT(AGGR(RANK(SUM({<[TrxType] = {'D'},ActionID = {'PICKCASE'},OperativeName=>}$(metric2))),OperativeName),1) = 2,'nd','th')),

//PICKED

NUM(SUM({< [TrxType] = {'D'},ActionID = {'PICKCASE'}>}$(metric2)),'#,##0'),

//% OVERALL

NUM((SUM({1}{<[TrxType] = {'D'},ActionID = {'PICKCASE'}>}$(metric2))

SUM({<[TrxType] = {'D'},ActionID = {'PICKCASE'},OperativeName=>}$(metric2))),'0.00%'))