topic Re: Expression Help in QlikView

Expression Help

satishkurra — Thu, 16 Jun 2016 20:29:47 GMT

I have the below pivot table data with Country, Service Lines and Sales%

Country	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19
A	40.0%	12.0%	6.0%	1.0%	-5.0%	12.0%	5.0%	-1.0%	6.0%	13.0%	-40.0%	35.0%	145.0%	9.5%	11.1%	-18.0%	245.2%	16.3%	-69.5%
B	1.0%	1.0%	3.0%	56.0%	45.0%	23.0%	8.0%	10.0%	6.0%	56.0%	34.0%	-10.0%	-20.0%	-30.0%	37.4%	90.0%	8.0%	6.0%	-70.0%

I need to show Increase Icon for highest % and nothing for other %

For example, for country A, highest % is 145% which should show icon and the rest all values for Country A should be blank.

I understand that we can do a custom format cells, but the value 145% is not constant and once data changes the highest % may go to other service line.

Request to please help on the same.

Thanks

Satish

Re: Expression Help

satishkurra — Thu, 16 Jun 2016 20:30:41 GMT

Sorry my bad, the highest % is 245% for Country A....

Re: Expression Help

sunny_talwar — Thu, 16 Jun 2016 20:32:04 GMT

Couple of questions:

1) Why do we need custom format cell to color a cell? Why not background color with condition

2) Isn't 245.2% is the highest (under 17) for Country A

Re: Expression Help

satishkurra — Thu, 16 Jun 2016 20:47:11 GMT

Hi Sunny

You are correct. We need to show 245% with an icon and the rest as blank

Ideally we only show 1 value for each country

Re: Expression Help

sunny_talwar — Thu, 16 Jun 2016 20:51:42 GMT

So you just wanted to see Country and next to it the highest value?

Dimension:

Country

Expression:

Max(Aggr(Expression, Country, [Service Lines]))

Re: Expression Help

satishkurra — Thu, 16 Jun 2016 20:53:36 GMT

Don't we need service line also as dimension

Re: Expression Help

sunny_talwar — Thu, 16 Jun 2016 20:58:19 GMT

I guess it depends on what you want to see? Do you want to see all service lines or just the max?

Re: Expression Help

satishkurra — Thu, 16 Jun 2016 21:01:42 GMT

All Service Lines

I'm trying to get this in below fashion. Only highest % should be displayed and rest all empty/blank

Country	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19
A																	245.2%
B																90.0%

Thanks

Satish

Re: Expression Help

sunny_talwar — Thu, 16 Jun 2016 21:07:53 GMT

How about this:

If(Sales % = max(Total <Country> Sales %), sales %, Dual(' ', 0.0001))

Or this if sales % is an expression:

If(sales % expression = max(Total <Country> Aggr(sales % expression, Country, service line)), sales % expression, Dual(' ', 0.0001))

Re: Expression Help

satishkurra — Thu, 16 Jun 2016 21:12:37 GMT

Hi Sunny

With the above expression, it is showing 0.0% for other than Max Value, Can we simply show blank instead of 0.0%?

Re: Expression Help

sunny_talwar — Thu, 16 Jun 2016 21:14:22 GMT

I think you might be using number fromatting from numbers tab, change that to expression default and use num function to format the max value in % format

Re: Expression Help

satishkurra — Thu, 16 Jun 2016 21:31:50 GMT

Thanks Sunny, It worked...