topic Re: Intersecting Values in QlikView

Intersecting Values

varunreddy — Mon, 19 Oct 2015 15:29:58 GMT

Hi Guys,

I am trying to create a bar chart and trying to get top 10 values using Dimension Limits. But the issue is selection of top 10 issues include these 3 values (BCBS, BCBS-Accuracy and BCBS-Timeliess), what ever position they are in.

Thanks in advance!

Regards,

Varun> K

Re: Intersecting Values

Oleg_Troyansky — Mon, 19 Oct 2015 16:04:02 GMT

Hi Varun,

if I understand correctly, you need to include three particular values in your chart, whether or not they fall into the Top 10 scenario...

In this case, Dimension Limits can't help you. You need to formulate your "Top 10" logic using advanced Set Analysis, and then append your three "fixed" values to the Top 10 set using the boolean union "+".

In order to formulate your Top 10 logic, read this blog:

QlikView Blog Q-Tip #5 – Show Top Performers. Common but not too trivial. | Natural Synergies

Then, to append the three values, you can use a variety of Set Analysis features. For example:

Sum({$<the Top 10 Condition> + $<the fixed three values>} Value)

Sum({<Product = P({<the top 10 condition>} Product) + P({<the fixed 3 condition>} Product) >} Value)

cheers,

Oleg Troyansky

Check out my new book QlikView Your Business: An expert guide to Business Discovery with QlikView and Qlik Sense

Re: Intersecting Values

varunreddy — Mon, 19 Oct 2015 16:13:15 GMT

Hi Oleg,

Expression I am using is this:

count({<[Pull Type] = {'W'}, [As of Date] = {'$(vmaxWeek)'}>}DISTINCT Id) .

What I understood is, I need to use Rank for above expression and perform union with those 3 mandatory values?

Regards,

Varun. K

Re: Intersecting Values

Oleg_Troyansky — Mon, 19 Oct 2015 16:18:58 GMT

yes, exactly!

Re: Intersecting Values

varunreddy — Mon, 19 Oct 2015 16:23:18 GMT

I tried something like this :

count

({<[Primary Issue Driver] = {"=rank([Primary Issue Driver]<=6)"},[Pull Type] = {'W'}, [As of Date] = {'$(vmaxWeek)'}>}DISTINCT Id)

This is not restricting the values to 6.

Could you have a look at this?

Re: Intersecting Values

Oleg_Troyansky — Mon, 19 Oct 2015 16:28:24 GMT

Your issue is hiding in the rank() function - what are you ranking there? Within the rank, you should list your aggregation function, not the dimension... Something like this:

count

({<[Primary Issue Driver] = {"=rank( count({<[Pull Type] = {'W'}, [As of Date] = {'$(vmaxWeek)'}>}DISTINCT Id))<=6)"}, [Pull Type] = {'W'}, [As of Date] = {'$(vmaxWeek)'}>}DISTINCT Id)

Then, you may or may not need to repeat the other SA filters...

cheers,

Oleg Troyansky

Check out my new book QlikView Your Business: An expert guide to Business Discovery with QlikView and Qlik Sense

Re: Intersecting Values

varunreddy — Mon, 19 Oct 2015 16:35:36 GMT

Thank you for the time. I will try to fix this issue.

Cheers,

Varun

Re: Intersecting Values

varunreddy — Mon, 19 Oct 2015 18:10:17 GMT

Hi Oleg,

I am able to get top 10 issues using your logic. How can I include three fixed values?

Regards,

Varun

Re: Intersecting Values

varunreddy — Mon, 19 Oct 2015 18:12:27 GMT

Expression I used:

count

({$<[Primary Issue Driver] = {'BCBS', 'BCBS-Accuracy', 'BCBS-Completeness', 'BCBS-Timeliness'}>
+ $<[Primary Issue Driver] = {"=rank(count({<[Pull Type] = {'M'}, [As of Date Month Year] = {'$(vmaxMonth)'}>}DISTINCT Id))<=6"},[Pull Type] = {'M'},[As of Date Month Year] = {'$(vmaxMonth)'}>} distinct Id)

Re: Intersecting Values

Oleg_Troyansky — Mon, 19 Oct 2015 18:14:38 GMT

How about something like this:

count

({<[Primary Issue Driver] = {"=rank( count({<[Pull Type] = {'W'}, [As of Date] = {'$(vmaxWeek)'}>}DISTINCT Id))<=10)"} + {'Name 1', 'Name2', 'Name 3'}

, [Pull Type] = {'W'}, [As of Date] = {'$(vmaxWeek)'}>}DISTINCT Id)

cheers,

Oleg Troyansky

Check out my new book QlikView Your Business: An expert guide to Business Discovery with QlikView and Qlik Sense

Re: Intersecting Values

varunreddy — Mon, 19 Oct 2015 18:17:44 GMT

Not Working. Displaying one value 'Name1'

Re: Intersecting Values

Oleg_Troyansky — Mon, 19 Oct 2015 18:22:56 GMT

Please post your app, it's really hard to guess what might be wrong.