topic Pivot table Color max and min issue in QlikView

Pivot table Color max and min issue

Zwax — Sat, 16 Nov 2024 03:05:22 GMT

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:

# 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?

Re: Pivot table Color max and min issue

Gallagher3Y — Fri, 05 Jul 2019 08:02:15 GMT

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.

Re: Pivot table Color max and min issue

Zwax — Mon, 08 Jul 2019 07:52:29 GMT

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.

Re: Pivot table Color max and min issue

Zwax — Tue, 09 Jul 2019 06:31:17 GMT

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