topic Re: Set Analysis with rank and filter in App Development

Set Analysis with rank and filter

samp — Thu, 16 Nov 2017 21:17:37 GMT

I am trying to add filter along with rank as below seems syntax is correct but I am not getting expected results any help?

Re: Set Analysis with rank and filter

sunny_talwar — Thu, 16 Nov 2017 22:58:33 GMT

Don't see anything wrong here....

Re: Set Analysis with rank and filter

Anil_Babu_Samineni — Fri, 17 Nov 2017 01:44:12 GMT

When you say values are not getting as expected. You may provide more detail the business. For a while, Remove one extra double quote and check?

sam pusala wrote:

I am trying to add filter along with rank as below seems syntax is correct but I am not getting expected results any help?

Count({<[Investment Strategy] = {"=Rank(Count({<Type = {'RFI','Database Search','Database Search Follow-Up'},Quarter={'Q1','Q2','Q3'},Year={""$(=Max(Year))""}>} [Investment Strategy]))<11" } > } [Investment Strategy])

Re: Set Analysis with rank and filter

sunny_talwar — Fri, 17 Nov 2017 12:14:50 GMT

Those double quotes are important loveisfail‌... they are used as Escape sequences

Re: Set Analysis with rank and filter

Anil_Babu_Samineni — Fri, 17 Nov 2017 13:00:59 GMT

Accept with you, But, Max(Year) always return one only only. Not sure how escape engage here?

Re: Set Analysis with rank and filter

sunny_talwar — Fri, 17 Nov 2017 13:04:13 GMT

I see what you are saying, in that case... I would remove both the double quotes

Count({<[Investment Strategy] = {"=Rank(Count({<Type = {'RFI','Database Search','Database Search Follow-Up'},Quarter={'Q1','Q2','Q3'},Year={$(=Max(Year))}>} [Investment Strategy]))<11" } > } [Investment Strategy])

Re: Set Analysis with rank and filter

Anil_Babu_Samineni — Fri, 17 Nov 2017 13:05:01 GMT

True

Re: Set Analysis with rank and filter

samp — Fri, 17 Nov 2017 15:02:48 GMT

Hi Sunny/Anil,

Here I am comparing below 2set analysis with only filter and with filter and rank

1) with only filter (results are correct)

Count({<Type = {'RFI','Database Search','Database Search Follow-Up'},Quarter={'Q1','Q2','Q3'},Year={"$(=Max(Year))"}>}[Investment Strategy])

(selecting manually fixed number and top 10, using qlik sense)

2) with filter and rank

(I assume that filters are not applying automatically, if I select same filters manually, result looks fine )

The 2) result is same as with simple count function except rank Count([Investment Strategy])

Not returning anything If I remove one double quote

Re: Set Analysis with rank and filter

sunny_talwar — Fri, 17 Nov 2017 15:06:49 GMT

What do you see when you create a chart with

Dimension

Investment Strategy

Expression

=Rank(Count({<Type = {'RFI','Database Search','Database Search Follow-Up'},Quarter={'Q1','Q2','Q3'},Year={""$(=Max(Year))""}>} [Investment Strategy]))<11

Re: Set Analysis with rank and filter

samp — Fri, 17 Nov 2017 15:18:04 GMT

returning all dashes (- - - -).

Re: Set Analysis with rank and filter

sunny_talwar — Fri, 17 Nov 2017 15:20:22 GMT

dashes? Can you show a screenshot?

Re: Set Analysis with rank and filter

samp — Fri, 17 Nov 2017 15:20:43 GMT

returning 0,-1 if I remove one double quote

Re: Set Analysis with rank and filter

samp — Fri, 17 Nov 2017 15:25:35 GMT

Re: Set Analysis with rank and filter

samp — Fri, 17 Nov 2017 15:29:38 GMT

my main requirement is I have to show top 10 Strategies who has more count, and corresponding consultants who has more count.

there is another post regarding same Top Customer vs Top Products

Re: Set Analysis with rank and filter

sunny_talwar — Fri, 17 Nov 2017 15:35:31 GMT

If this is what you are getting after removing one of the double quotes, then I don't see what the issue might be

Re: Set Analysis with rank and filter

samp — Fri, 17 Nov 2017 22:10:36 GMT

Hi Sunny,

it is working if add filters both places as below:

working fine:

Count({<[Investment Strategy]={"=Rank(Count({<Type = {'RFI','Database Search','Database Search Follow-Up'},Quarter={'Q1','Q2','Q3'},Year={""$(=Max(Year))""}>} [Investment Strategy]))<11" },Type = {'RFI','Database Search','Database Search Follow-Up'},Quarter={'Q1','Q2','Q3'},Year={"$(=Max(Year))"}>}[Investment Strategy])

I am trying to achieve my final result, only issue is not getting correct data for top 2 customer but ok for top 1 customer.

below adding sample inline data and my correct exp.

OK Dim: product

OK Tot Exp:

count({<product = {"=Rank(count({<Quarter = {'Q1','Q2'}>}product),4)< 4"}, Quarter = {'Q1','Q2'} >}product)

OK top1 Exp:

count({<product = {"=Rank(count({<Quarter = {'Q1','Q2'}>}product),4)< 4"},

customer = {"=Rank(Count({<product = {""=Rank(count({<Quarter = {'Q1','Q2'}>}product),4)< 4""},Quarter = {'Q1','Q2'}>} product)) = 1"},

Quarter = {'Q1','Q2'} >}product)

NOK Top 2 ExP:

count({<product = {"=Rank(count({<Quarter = {'Q1','Q2'}>}product),4)< 4"},

customer = {"=Rank(Count({<product = {""=Rank(count({<Quarter = {'Q1','Q2'}>}product),4)< 4""},Quarter = {'Q1','Q2'}>} product)) = 2"},

Quarter = {'Q1','Q2'} >}product)

below is inline:

custTable:
Load * Inline [
customer, product, Quarter

'abc', 'p1', 'Q1'
'abc', 'p1', 'Q1'
'abc', 'p1', 'Q2'
'abc', 'p2', 'Q2'
'abc', 'p3', '44'
'abc', 'p4', 'Q1'

'pqr', 'p1', 'Q2'
'pqr', 'p2', 'Q2'
'pqr', 'p1', 'Q2'
'pqr', 'p2', '44'
'pqr', 'p5', 'Q1'

'xyz', 'p5', '11'
'xyz', 'p3', '11'
'xyz', 'p3', '11'
'xyz', 'p5', '11'

'xyz', 'p2', 'Q1'
'xyz', 'p6', 'Q1'
'xyz', 'p5', 'Q1'

'EEE', 'p3', 'Q1'
'EEE', 'p3', 'Q1'
'EEE', 'p3', 'Q1'

'DDD', 'p4', 'Q1'

'DDD', 'p3', 'Q1'

];