Highlighting top n values in each column of a table

[linoyel]

Hey all!

I have a straight table with calculated expressions (presented in percents).

2 questions:

1. How to add min, max and average for each column?

2. How to highlight top 5 values in each column? (say if I have 5 columns, I need 25 values to be highlighted)

3. How to highlight today's day? (see in the below example that "22" is red)

Thank you!

Below is the example of how my table is supposed to look like:

Day / Billing Country	Saudi Arabia	United Arab Emirates	Turkey	United States	Egypt	France	South Africa	Russia	Singapore	Qatar	Kuwait	Malaysia	Nigeria	Brazil	Italy	Lebanon	United Kingdom	Venezuela
1	48%	47%	61%	68%	33%	44%	37%	28%	48%	35%	32%	32%	9%	27%	36%	36%	29%	55%
2	51%	39%	49%	69%	31%	48%	30%	31%	58%	39%	36%	32%	30%	49%	33%	30%	43%	52%
3	43%	46%	53%	56%	34%	51%	33%	30%	56%	41%	26%	43%	21%	49%	29%	33%	38%	47%
4	49%	44%	52%	59%	28%	51%	32%	28%	61%	42%	41%	42%	19%	50%	44%	16%	48%	55%
5	44%	42%	53%	56%	34%	61%	35%	27%	54%	34%	38%	37%	8%	48%	29%	33%	47%	55%
6	44%	46%	52%	63%	35%	49%	30%	30%	51%	39%	30%	31%	40%	56%	34%	45%	48%	51%
7	46%	40%	51%	59%	36%	55%	28%	29%	51%	44%	28%	25%	28%	63%	35%	31%	40%	55%
8	44%	37%	50%	70%	31%	53%	30%	36%	45%	36%	32%	32%	16%	43%	30%	30%	41%	59%
9	43%	40%	54%	66%	30%	59%	36%	31%	57%	42%	23%	39%	18%	40%	31%	25%	38%	50%
10	45%	44%	57%	61%	40%	53%	31%	28%	50%	30%	29%	29%	8%	41%	43%	29%	36%	45%
11	47%	44%	51%	60%	29%	59%	32%	33%	47%	43%	33%	44%	40%	31%	44%	24%	28%	63%
12	45%	41%	53%	68%	27%	63%	37%	29%	41%	40%	40%	43%	29%	65%	44%	31%	39%	44%
13	43%	37%	55%	52%	24%	58%	32%	33%	43%	28%	33%	27%	25%	54%	36%	34%	32%	43%
14	41%	38%	51%	65%	33%	48%	28%	30%	41%	48%	24%	36%	12%	33%	42%	32%	49%	55%
15	43%	45%	61%	65%	34%	44%	33%	28%	47%	48%	34%	39%	9%	52%	40%	27%	36%	58%
16	49%	46%	60%	61%	33%	57%	39%	34%	47%	26%	34%	30%	39%	41%	43%	31%	44%	55%
17	54%	46%	55%	65%	38%	48%	28%	32%	47%	32%	32%	33%	27%	59%	25%	34%	46%	42%
18	46%	40%	56%	69%	24%	51%	30%	28%	44%	33%	22%	35%	25%	58%	33%	35%	10%	49%
19	46%	38%	57%	62%	27%	48%	24%	35%	49%	37%	32%	39%	46%	55%	20%	31%	48%	57%
20	44%	38%	52%	64%	27%	45%	36%	29%	38%	43%	33%	25%	27%	50%	20%	19%	26%	53%
21	43%	38%	45%	55%	30%	53%	28%	34%	33%	39%	25%	17%	5%	28%	49%	29%	28%	54%
22	45%	42%	52%	64%	31%	39%	32%	30%	41%	31%	28%	40%	23%	40%	33%	30%	25%	49%
23	40%	33%	56%	65%	38%	38%	31%	32%	34%	31%	45%	40%	33%	35%	22%	29%	32%	55%
24	43%	40%	51%	64%	34%	49%	37%	30%	40%	32%	34%	42%	0%	43%	34%	32%	39%	48%
25	45%	35%	54%	61%	30%	45%	30%	31%	45%	31%	27%	35%	20%	42%	37%	28%	61%	47%
26	49%	38%	49%	67%	27%	47%	29%	36%	41%	40%	19%	22%	17%	50%	21%	24%	42%	55%
27	43%	40%	53%	71%	34%	44%	34%	33%	28%	32%	19%	32%	18%	53%	35%	34%	51%	43%
28	42%	39%	47%	69%	36%	45%	38%	35%	35%	36%	36%	33%	16%	38%	33%	33%	39%	50%
29	46%	47%	52%	59%	34%	48%	33%	33%	55%	36%	33%	25%	16%	27%	40%	36%	28%	49%
30	41%	42%	47%	63%	41%	50%	34%	31%	45%	52%	43%	26%	29%	45%	41%	25%	34%	51%
31	41%	41%	50%	65%	34%	50%	29%	25%	56%	42%	36%	47%	29%	82%	32%	42%	67%	44%
min	40%	33%	45%	52%	24%	38%	24%	25%	28%	26%	19%	17%	0%	27%	20%	16%	10%	42%
max	54%	47%	61%	71%	41%	63%	39%	36%	61%	52%	45%	47%	46%	82%	49%	45%	67%	63%
avg	45%	41%	53%	63%	32%	50%	32%	31%	46%	38%	32%	34%	22%	47%	35%	31%	39%	51%

[linoyel]

Hey guys!

Thank you very much for your answers!

Great approach with crosstable, though in my case it's not possible to apply it cause every country in my case is a complicated expression with set analysis.

But the formulas for Background Color, Text Color and Format worked!!! - just without "sum".

In each expression I entered this:

For example - for Saudi Arabia -

1. Background Color:

if(rank([Saudi Arabia])<=5,LightGreen(50))

2. Text Color:

if(rank([Saudi Arabia])<=5,LightRed())

3. Text Format:

if(rank([Saudi Arabia])<=5,'<b>')

The same I did with Day dimension in order to emphasize today's day:

1. Background Color:

=if(Calendar_TRX.Day=day(Today()),LightRed(50))

2. Text Color:

=if(Calendar_TRX.Day=day(Today()),red())

3. Text Format:

=if(Calendar_TRX.Day=day(Today()),'<b>')

Still not sure how to apply MIN, MAX and AVG, trying to figure it out...

In expression tab --> Total Mode I can choose only one total but I need 3 totals to show...

Any suggestions?

[Gysbert_Wassenaar]

See attached qvw for two options.

[linoyel]

Thank you very much, Gysbert! I learned a lot from your examples

But still, I'm not sure how to apply the pick(match...) formula in my case where it's not a crosstable but Days (1-->31) is a dimension and all the countries are separate expressions

(the example of the calculation for each country is:

Count({[AppRate-ONLINE] * 1<BillingCountryKey = {161}>} DISTINCT if(cctranzresponse=000 AND Table_No=1,unique_num,NULL())) / Count({[AppRate-ONLINE] * 1<BillingCountryKey = {161}>} DISTINCT if(Table_No=1,unique_num,Null()))

)

[Gysbert_Wassenaar]

Can you post a document with sample data that matches your real data model? See this document for how to do that safely: Preparing examples for Upload - Reduction and Data Scrambling

[linoyel]

It has a huge data model.. I'll try

Max size to upload here is 300 MB, my file is 860 MB

[linoyel]

Attached

[Gysbert_Wassenaar]

I'm unable to open the document you uploaded. I suspect it got corrupted during the upload. It's also rather big still. Can you reduce it further? Perhaps you could select 6 days instead of leaving all 31 days available. That could reduce the data by about 80% (if the data is spread evenly over the days).

[linoyel]

I donno how to do it.. the document contains data of several years, it's a huge data model and if I reduce it in the scripting, I won't get the results of the calculation in my report...  If I use File-->Reduce Data option, it reduces the file from 861 MB to 691 MB, and if I put it in ZIP folder - it's 615 MB - still too big to upload

[PrashantSangle]

Hi,

I am not sure but read this article.

http://community.qlik.com/blogs/qlikviewdesignblog/2013/12/09/pareto-analysis

Posted by Henric Cronström

This will help you.

Regards

[Gysbert_Wassenaar]

Try selecting just a few values in a dimension first. Then choose File->Reduce Data and select the option Keep Possible Values. You seem to have a Day dimension. If you first select 6 days out of the 31 possible and then reduce the data by keeping only the 6 possible Day values you should see a reduction of about 80% (assuming the data is spread evenly over the days).