topic RANK ISSUE WHEN USING TWO DIMENSION VALUES in QlikView

RANK ISSUE WHEN USING TWO DIMENSION VALUES

wale_martins — Thu, 22 Mar 2018 09:00:50 GMT

Hi there,

I have a problem when using a rank function in a straight table. The thing is that I want to rank the first top 13, taking into consideration two dimension values (TRACKING,TAG).

My problem comes when there's a duplicate TAG but with different TRACKING. It seems like if my rank expression, it's only taking into consideration the top 13 TAG values (without caring the TRACKING dimension) to create the ranking.

So in that case, appear the top 13 TAG values (Formacion and Jornada Formacion Anestesia are duplicated) but not the 13 top TRACKING,TAG values.

DIMENSIONS:

TRACKING;

TAG

EXPRESSIONS:

COUNT TAGS => IF(AGGR(RANK(COUNT(DISTINCT ID_VISITA),4),TRACKING,TAG)<=13,AGGR(COUNT(DISTINCT ID_VISITA),TRACKING,TAG))

Any help would be appreciated.

Thanks!!

Wale

Re: RANK ISSUE WHEN USING TWO DIMENSION VALUES

thevingo — Thu, 22 Mar 2018 10:04:06 GMT

1. Can you take both Tag & Tracking by concating as dimension, then use in AGGR formula as you've given then hide that dimension/column from Presentation tab.

Formula can be IF(AGGR(RANK(COUNT(DISTINCT ID_VISITA),4),TRACKING&TAG)<=13,AGGR(COUNT(DISTINCT ID_VISITA),TRACKING,TAG))

2. Or I think you can try rank before aggr Rank(Aggr

Re: RANK ISSUE WHEN USING TWO DIMENSION VALUES

wale_martins — Thu, 22 Mar 2018 10:40:37 GMT

Hi Govind,

1. I need both dimension values as dimension (Filtering issue). Concating and adding TAG as an expression AND TRACKING as an expression it's not viable in that case...

2. It doesn't work...

Thank you for your time!

Re: RANK ISSUE WHEN USING TWO DIMENSION VALUES

sunny_talwar — Thu, 22 Mar 2018 11:13:40 GMT

Your Count seems to be a really small number... is it possible that they are all getting ranked the same and hence not getting picked?... would you be able to share a sample to look at this in detail?

Re: RANK ISSUE WHEN USING TWO DIMENSION VALUES

wale_martins — Thu, 22 Mar 2018 11:45:20 GMT

Hi Sunny!

The problem appear when there is a duplicated TAG.... For example, If we do not take into consideration the TRACKING dimension, the rank is correct. It picks only 13 values... the problem appears when I try to add TRACKING as dimension and there is a repeated TAG....

Re: RANK ISSUE WHEN USING TWO DIMENSION VALUES

sunny_talwar — Thu, 22 Mar 2018 11:49:03 GMT

Does this look right?

I used this

IF(AGGR(RANK(TOTAL COUNT(DISTINCT ID_VISITA),4),TRACKING,TAG)<=13,AGGR(COUNT(DISTINCT ID_VISITA),TRACKING,TAG))

Re: RANK ISSUE WHEN USING TWO DIMENSION VALUES

wale_martins — Thu, 22 Mar 2018 12:02:10 GMT

Genious!!! I don't know how many hours I've wasted trying to do the trick!! 5 Sunny minutes and it's solve!

Thank you very much!!!