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

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
TocogroupAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RobOwner (Aidellio)Commented:
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
TocogroupAuthor Commented:
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
RobOwner (Aidellio)Commented:
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
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

TocogroupAuthor Commented:
I'm using different data as my example. The QV application is called Sales and the horizontal dimension is Employee ID.
0
RobOwner (Aidellio)Commented:
ok - will be looking at it soon
0
RobOwner (Aidellio)Commented:
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
TocogroupAuthor Commented:
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
RobOwner (Aidellio)Commented:
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
TocogroupAuthor Commented:
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
RobOwner (Aidellio)Commented:
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
TocogroupAuthor Commented:
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
RobOwner (Aidellio)Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TocogroupAuthor Commented:
Yes, that works ! So the syntax is similar to the Excel Nested IF statement.
Thanks again
0
RobOwner (Aidellio)Commented:
That's right. Probably the best way to describe it :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Software

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.