Intersecting Values

[varunreddy]

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

[Oleg_Troyansky]

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)

or

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 an...

[varunreddy]

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

[Oleg_Troyansky]

yes, exactly!

[varunreddy]

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?

[Oleg_Troyansky · Accepted solution]

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 an...

[varunreddy]

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

Cheers,

Varun

[varunreddy]

Hi Oleg,

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

Regards,

Varun

[varunreddy]

  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)

[Oleg_Troyansky]

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 an...