complex heat map

[#1]

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!

[#2]

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!

[rustyfishbones]

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,

                                        Expense,

                                        Expense,

                                        Expense)

[#4]

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

[#5]

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?

[#6]

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:

[#7]

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!

[#8 · Accepted solution]

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

[#9]

Thank you so much for your help!

[rustyfishbones]

Add a Label

[#11]

No problem