topic Re: Problem with set analysis in QlikView

Problem with set analysis

kamal_sanguri — Mon, 02 Apr 2018 18:33:11 GMT

I am not able to figure out the problem with this Set Expression:

=count({<Author_Name = {"=rank(count({<Word_Count = {'>250'}>}Stories))<=5"}>}Stories)

I have written it to count the number of stories where Word_Count is over 250 with top 5 authors... This expression returns the number but not correct.. it is ignoring condition to check word_count > 250. Even if I change 250 to 100 there is no difference.

Re: Problem with set analysis

vishsaggi — Mon, 02 Apr 2018 18:40:23 GMT

Can you try this and check?

=count({<Author_Name = {"=rank(count(IF(Word_Count > 250, Stories)) <=5"}>} Stories)

Re: Problem with set analysis

kamal_sanguri — Mon, 02 Apr 2018 18:46:20 GMT

Thanks for checking this..

yes after adding missing ")" it is working

=count({<Author_Name = {"=rank(count(IF(Word_Count > 250, Stories))) <5"}>} Stories)

I understand that I can use IF instead but I am more curious to know where is the problem with my expression?

Re: Problem with set analysis

vishsaggi — Mon, 02 Apr 2018 18:50:29 GMT

What values does your Word_Count holds? Can you share a sample to look into?

Re: Problem with set analysis

kamal_sanguri — Mon, 02 Apr 2018 18:52:37 GMT

! !

Re: Problem with set analysis

sunny_talwar — Mon, 02 Apr 2018 19:00:18 GMT

I think the two expressions are slightly different... one is < 5 and other is <= 5...If I change set analysis to < 5.. it gives 78352 also

=Count({<Author_Name = {"=rank(count({<Word_Count = {[>250]}>}Stories))<5"}>}Stories)

Re: Problem with set analysis

vishsaggi — Mon, 02 Apr 2018 19:00:23 GMT

Try this expr: you were using <= 5 rather than < 5 so just removed =.

=count({<Author_Name = {"=rank(count({<Word_Count = {'>250'} >}Stories))< 5 "}>} Stories)

Re: Problem with set analysis

vishsaggi — Mon, 02 Apr 2018 19:03:06 GMT

Thanks for reminding. Its been quite a while I used [] brackets inside set.

Re: Problem with set analysis

sunny_talwar — Mon, 02 Apr 2018 19:04:32 GMT

This is my first time

Re: Problem with set analysis

sunny_talwar — Mon, 02 Apr 2018 19:05:26 GMT

Never had to use it before, but with the introduction of stricter quoteology, we might have to use it more often

Re: Problem with set analysis

vishsaggi — Mon, 02 Apr 2018 19:07:35 GMT

Absolutely.

Re: Problem with set analysis

kamal_sanguri — Tue, 03 Apr 2018 02:44:45 GMT

Hi Sunny, hope u are well.. sorry for the confusion (<5 or <=5). However the issue still persists:

=Count({<Author_Name = {"=rank(count({<Word_Count = {[>250]}>}Stories))<5"}>}Stories)

Problem with set analysis

Even with the your expression, it is ignoring Word_Count filter. Even if I make it 100 or any other number.. expression gives the same result.

Re: Problem with set analysis

kamal_sanguri — Tue, 03 Apr 2018 03:03:10 GMT

I tried all these variants :

=count({<Author_Name = {'=rank(count({<Word_Count = {">250"}>}Stories))<=5'}>}Stories)

=count({<Author_Name = {"=rank(count({<Word_Count = {'>250'}>}Stories))<=5 "}>}Stories)

=Count({<Author_Name = {"=rank(count({<Word_Count = {[>250]}>}Stories))<=5"}>}Stories)

=count({<Author_Name = {"=rank(count({<Word_Count = {"">250""}>}Stories))<=5"}>}Stories)

they all give same result..

Re: Problem with set analysis

kamal_sanguri — Tue, 03 Apr 2018 03:13:50 GMT

22469+20744+20659+14480+10416 = 88768 this is the number that I am getting with all the above mentioned expression.

17202+15977+15849+11071+8090=68189. This is the number which I am expecting.. with filter of word count more than 250.

Re: Problem with set analysis

Digvijay_Singh — Tue, 03 Apr 2018 03:21:51 GMT

You may try this as well -

=count({<Author_Name = {"=rank(Aggr(count({<Word_Count = {'>250'} >}Stories),Author_Name))< 5 "}>} Stories)

Re: Problem with set analysis

kamal_sanguri — Tue, 03 Apr 2018 03:35:43 GMT

Thanks but same result.. as if it is ignoring or not understanding Word_Count<250

Re: Problem with set analysis

kamal_sanguri — Tue, 03 Apr 2018 03:36:56 GMT

same story, even if you make 250 as 100 you get same result.

Re: Problem with set analysis

Digvijay_Singh — Tue, 03 Apr 2018 03:52:06 GMT

See if this makes any difference or else share the sample data to try further -

=count({<Author_Name= {"=rank(count({<Word_Count = {[>250]} >}Stories))< 5"},Word_Count = {">250"}>} Stories)

Re: Problem with set analysis

kamal_sanguri — Tue, 03 Apr 2018 03:58:36 GMT

stalwar1‌ swuehl‌

I have get it working using expression

if(rank(sum(if(Word_Count>250,1)))<=5,sum(if(Word_Count>250,1)))

but still not able to understand why the set expression not working.. if you could spend few moments to review it again.

Regards,

-Kamal

Re: Problem with set analysis

kamal_sanguri — Tue, 03 Apr 2018 04:20:38 GMT

yeah that worked, I see you intelligently used an additional filter to filter the data set.