Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Zwax
Contributor III
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?

 

Labels (4)
1 Solution

Accepted Solutions
Zwax
Contributor III
Contributor III
Author

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
Contributor
Contributor

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
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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()
))