Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Zwax
New Contributor III

Pivot table Color max and min issue

Hi all you gurus

I’m new to QlikView, and need a little help with a problem.

In a pivot table, I want to mark the highest and the lowers value with colors. That should probably be easy enough – however a want to do this within the different dimensions and would also like to show the highest total. Is that possible?

- And do I need to handle something when the number might not be unique? Let’s say for example the lowest number is 30, but we got that number represented more than one time?

I use a table to test this from a course I took, so some of you might know it.

I want it to look something like this:

2019-07-05 07_32_00-QlikView.png

 

# Active customers got the following expression: Count(DISTINCT CustomerRecordCounter)

 

I set the expression in the background color.

 

I have tried some different stuff, but without any luck – jet.

 Different attempts:

 

//if(Count(CustomerRecordCounter) = Max( Total CustomerRecordCounter), Red(),
//  if(Count(CustomerRecordCounter) = Min( Total CustomerRecordCounter), Green()))
  
  
//If(Count(CustomerRecordCounter)=max(Total <Year>CustomerRecordCounter),
//	Red(),
//	If(Count(CustomerRecordCounter)=min(Total <Year>CustomerRecordCounter),
//		Green())
//				)

//If(RangeMax(CustomerRecordCounter), Red())


//IF(Sum([CustomerRecordCounter])=RANGEMAX(TOP(TOTAL Sum([CustomerRecordCounter]),1,NOOFROWS(TOTAL))),Red())

Any ideas to accomplish this?

 

1 Solution

Accepted Solutions
Zwax
New Contributor III

Re: Pivot table Color max and min issue

Solved it by adding logic to avoid null values:

If(count(CustomerRecordCounter) = Max(total<Year>Aggr(count(CustomerRecordCounter), Year, Month)), LightRed()
,If(count({< [CustomerRecordCounter] = {'<>0'} >} [CustomerRecordCounter]) = Min(total<Year>Aggr(count({< [CustomerRecordCounter] = {'<>0'} >} [CustomerRecordCounter]), Year, Month)), LightGreen()
))

View solution in original post

3 Replies
Gallagher3Y
New Contributor

Re: Pivot table Color max and min issue

You can calculate averages, counts, max/min values or sums for numbers in a group. Let's add some colors, to see the difference in these tables better prepaidgiftbalance.

Zwax
New Contributor III

Re: Pivot table Color max and min issue

So I'm a bit further now...

Solved it by the following expression in background color:

If(count(distinct CustomerRecordCounter) = Max(total<Year>Aggr(count(distinct CustomerRecordCounter), Year, Month)), LightRed()
,If(count(distinct CustomerRecordCounter) = Min(total<Year>Aggr(count(distinct CustomerRecordCounter), Year, Month)), LightGreen()
))

In my test-data it works great. Highlights min and max as wanted.

However, when I move on to some other data the min function stops working. Max still gives me what I want. My guess would be that it is somehow related to null values, but I'm not sure.

Can anyone help me?

Not sure what logic to put where if it's related to null values

....or if it is an entirely different issue.

Zwax
New Contributor III

Re: Pivot table Color max and min issue

Solved it by adding logic to avoid null values:

If(count(CustomerRecordCounter) = Max(total<Year>Aggr(count(CustomerRecordCounter), Year, Month)), LightRed()
,If(count({< [CustomerRecordCounter] = {'<>0'} >} [CustomerRecordCounter]) = Min(total<Year>Aggr(count({< [CustomerRecordCounter] = {'<>0'} >} [CustomerRecordCounter]), Year, Month)), LightGreen()
))

View solution in original post