Using AGGR in PICK MATCH issue

[rustyfishbones]

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

[swuehl]

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

A Lot of AGGR Functions in a Quartile Chart

[Oleg_Troyansky]

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

[rustyfishbones]

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

[Oleg_Troyansky]

Hi Alan,

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

cheers,

Oleg Troyansky

[rustyfishbones]

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

[rustyfishbones]

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

[rustyfishbones]

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%'))