Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How do I apply conditions to individual columns in a QlikView pivot table heat map ?

Posted on 2014-08-29
15
Medium Priority
?
2,697 Views
Last Modified: 2016-08-29
Hi,

I have a QlikView dashboard with a pivot table displaying as a heat map. Currently, the conditions test against the cell values for the whole of the pivot table (including the Totals column).

So, for example, my horizontal axis (columns) uses a dimension called 'Lesson type', which displays as 10 columns. I would like to apply a different test on each column, rather than a single test on all columns.

Is it possible to apply different tests to specific columns ?

Thanks
Toco
0
Comment
Question by:Tocogroup
  • 8
  • 7
15 Comments
 
LVL 43

Expert Comment

by:Rob
ID: 40294376
Can you scramble your data and attach it here?  I haven't used a heat map but I may be able to do some testing if I've got a basic structure to work with.
0
 

Author Comment

by:Tocogroup
ID: 40294593
Hi,

I've attached the Excel workbook which holds the data sheet, and the pivot table I've recreated in the QV dashboard.

A matter of interest - why won't EE allow me to upload a QVW file. It's complaining about the file extension ??
TocoSales.xlsx
0
 
LVL 43

Expert Comment

by:Rob
ID: 40294966
You can upload it here: http://ee-stuff.com/Expert/Upload/upload.php

Then paste the link here.

QVW isn't on the list of allowed extensions, probably because EE doesn't know about Qlikview.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:Tocogroup
ID: 40296250
I'm using different data as my example. The QV application is called Sales and the horizontal dimension is Employee ID.
0
 
LVL 43

Expert Comment

by:Rob
ID: 40296269
ok - will be looking at it soon
0
 
LVL 43

Expert Comment

by:Rob
ID: 40296402
What was the test you want on each column?  Because it's the combination of expression and dimension.  What's the logic behind what you want to show?  Just trying to get an idea of what you want.
0
 

Author Comment

by:Tocogroup
ID: 40296572
For example, in column 1 I might want the following conditional formatting...
If ([SPI Status] >= 250, LightRed(),
   If ([SPI Status] >= 100, Yellow(),
   Green()))

In column 2, however, I might want the following...
For example, in column 1 I might want the following conditional formatting...
If ([SPI Status] >= 100, LightRed(),
   If ([SPI Status] >= 50, Yellow(),
   Green()))

The field remains the same but the values vary from column to column, if that makes sense.
0
 
LVL 43

Expert Comment

by:Rob
ID: 40296597
Then you also have to add the condition for what the [column] value will be
If ([column] == X, 
    If ([SPI Status] >= 100, LightRed(),
        If ([SPI Status] >= 50, Yellow(),Green())
    ),
if ([column] == Y,
    If ([SPI Status] >= 250, LightRed(),
        If ([SPI Status] >= 100, Yellow(),Green())
    )
)
...

Open in new window

Unfortunately, it's not very elegant.  If you could work out a general formula then I have used Match(), WildMatch, MixMatch() to get what I want.
0
 

Author Comment

by:Tocogroup
ID: 40296770
Ok. How do I reference the column ? I'm not sure how to code this.

So, for example, because I am using EmployeeID as the column header in my pivot table, the value (the EmployeeID in other words) in the column header of column 1 could be, 10023.

How do I convert your If ([column] == X using the above ?
0
 
LVL 43

Expert Comment

by:Rob
ID: 40297577
In your demo qvw, this worked on the Background Color where you have your existing definitions

If (EmployeeID = 5,
If (NoOfProducts >= 10, LightGreen(),
   If (NoOfProducts >= 2, Black(),
   LightRed())),LightBlue()
   )
0
 

Author Comment

by:Tocogroup
ID: 40297864
Yes that works ! How do I code for subsequent columns ? I tried using an Else clause but it didn't like it. Similarly it didn't like me repeating your code beneath the first nested IF but with a different EmployeeID test.

Any ideas ?
0
 
LVL 43

Accepted Solution

by:
Rob earned 2000 total points
ID: 40298055
If (EmployeeID = 5,
    If (NoOfProducts >= 10, LightGreen(),If (NoOfProducts >= 2, Black(),LightRed()))
    ,If (EmployeeID = 6,
        If (NoOfProducts >= 10, LightBlue(),If (NoOfProducts >= 2, Black(),LightRed()))
....

)
0
 

Author Closing Comment

by:Tocogroup
ID: 40298283
Yes, that works ! So the syntax is similar to the Excel Nested IF statement.
Thanks again
0
 
LVL 43

Expert Comment

by:Rob
ID: 40298288
That's right. Probably the best way to describe it :)
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you are like me and like multiple layers of protection, read on!
Eseutil Hard Recovery is part of exchange tool and ensures Exchange mailbox data recovery when mailbox gets corrupt due to some problem on Exchange server.
XMind Plus helps organize all details/aspects of any project from large to small in an orderly and concise manner. If you are working on a complex project, use this micro tutorial to show you how to make a basic flow chart. The software is free when…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question