topic Re: complex heat map in QlikView

complex heat map

Thu, 23 May 2013 20:35:48 GMT

Hi,

I have a mental picture of how to make a cool heat map - I made it in Excel but have no idea whether QV will support it. Attached on the first page is the excel data (both raw and relative). The second page is the desired output. The third page is a matrix convert of the raw data so that QV will read it.

Does anyone know whether this is feasible in QV?

Thanks!

Re: complex heat map

rustyfishbones — Thu, 23 May 2013 20:51:49 GMT

Yes it is feasible

here is a video on how it can be done

https://www.youtube.com/watch?v=Ngwc_sLnRoI

Re: complex heat map

Thu, 23 May 2013 21:21:18 GMT

See attached.

Re: complex heat map

Thu, 23 May 2013 21:23:50 GMT

that's insanely cool that you did that so fast!! I'll try to replicate with my full data set and hopefully it will work. I will let you know if it does Thank you both for your help!

Re: complex heat map

Thu, 23 May 2013 21:57:36 GMT

Ok, it's coming together really well but I still have a couple of problems:

1. I have 2000 buyers in my real data set - I only want to show the top 5 by total discretionary spend.. is there a way to do that in this pivot?

2. I have 89 expenses in my real data set - I only want to show specific ones. Is there a way to filter the pivot to only show the ones I want to show?

Again - this is the coolest chart - thanks so much!

Re: complex heat map

rustyfishbones — Thu, 23 May 2013 22:28:32 GMT

What version of Qlikview are you using?

Re: complex heat map

Thu, 23 May 2013 22:30:01 GMT

v11

Re: complex heat map

rustyfishbones — Thu, 23 May 2013 22:35:12 GMT

So will this help, in your case the Dimension will be Buyers

Re: complex heat map

Thu, 23 May 2013 23:48:45 GMT

sorry - I dont think there are dimension limits in a pivot? I figured that writing a calculated dimension may work but I couldn't get the syntax correct...

Re: complex heat map

rustyfishbones — Fri, 24 May 2013 08:30:02 GMT

Apologies

try this as an expression

IF(AGGR(RANK(SUM(Revenue)),Expense,Buyers) <= 5,SUM(Revenue))

Re: complex heat map

Fri, 24 May 2013 12:57:39 GMT

For some reason this isn't working yet for me... If I read the expression, it's saying rank the Buyers by the sum of revenue, and and if they are less than or equal to five, sum the revenue.

What is the role of the expense variable in the function?

Sorry for all the questions and thanks as always for the help!

Re: complex heat map

rustyfishbones — Fri, 24 May 2013 13:02:31 GMT

I will look at the test.qvw, but that should work

I will check shortly

You could also try the PICK MATCH functions but they are not dynamic

=PICK(MATCH(Expense,'Security Guards','Consulting','Training','IT Consulting','Sponsorship'),

SUM(Revenue),

Expense,

Expense)

Re: complex heat map

Fri, 24 May 2013 13:41:39 GMT

To limit the number of buyers, just use an if statement in the Calculated Dimension field for Buyers. See attached.

Re: complex heat map

Fri, 24 May 2013 13:47:21 GMT

Thanks! That has sorted the buyers perfectly! one last thing - now I have a column header that shows =if(aggr).... due to the calculated dimension. Is there a way to remove that column the way you had done it so nicely?

Re: complex heat map

Fri, 24 May 2013 13:50:26 GMT

The column is actually still there, I just hid the label by replacing it with a space in the label of the if(aggr... dimension. Put the space here:

Re: complex heat map

Fri, 24 May 2013 13:54:53 GMT

Hmm... I realized one thing about the dimension calculation - it shows the 5 buyers, but then it also shows a blank column summing up all the buyers that are not in the top 10. is there a workaround to hide the "other" column? thanks!

Re: complex heat map

Fri, 24 May 2013 13:55:53 GMT

Yes, you need to check the box that says 'Suppress when value is null' for that dimension.

Re: complex heat map

Fri, 24 May 2013 13:58:21 GMT

Thank you so much for your help!

Re: complex heat map

rustyfishbones — Fri, 24 May 2013 14:00:34 GMT

Add a Label

Re: complex heat map

Fri, 24 May 2013 14:00:47 GMT

No problem