Bill Lay showed this one in his Tips & Tricks session at the “Masters Summit for QlikView” in San Francisco.

How do you show a currency symbol on chart y-axis values?  It requires two steps:

1. Assign the format, like Money, in the Number format.

2. Here’s the trick — set a value for the axis “Static Step”. 

The resulting chart will include the “$” on each value of the axis.

There is an odd bit about this behavior. If the chart is not tall enough to show the Static Step increment, QV will revert to not showing the “$”. I’ll reduce the height of this chart to demonstrate.

You can display units other than currencies,  for example meters. Use the text in the Number format like “#,##0 meters”.

One more odd bit to be aware of. Remember I said the text part will not display if the chart is too short? An exception is made if the chart is tall enough for every line on the axis to have a value. If every line is filled, the text is shown, regardless of the increment used.

Let me demonstrate with the Length chart. I’ll set the Static Step property to “1”.  As expected, the “meters” text will not display because the chart is not tall enough to show increments of 1 meter.

Now watch what happens when I increase the height of the chart:

As soon as every line of the y-axis contains a label, “meters” appears, even though the increment is still much larger than 1.

Join us at a future Masters Summit for more tips from Dr Bill. I will be presenting along with the rest of the Masters crew in New York City in September and Copenhagen in October. See you there!

-Rob

Last month  I made an appeal in this blog for developers to run a tool that would scan QVWs and contribute anonymous data to a study. Sixty one sites have submitted data so far. As promised, I’m making the results available for download and analysis here.

It turns out to be a pretty interesting data set — if you are into QV. The available data is limited by a few design points of the collection:

1. All returned data had to be absolutely anonymous. This required that counting be done in the scan. I could not bring back the actual expression text.

2. The scanner should not open the document. This means that script  was not examined. Only data available in the QVW XML was used.

As a representative sample of the QV world, the sample size is rather small and probably suffers from a significant selection bias.

Please also note some specifics of the counting. Functions in variables were counted, but only once. “Sum()” in a variable was counted as 1 use of Sum, even if the variable was used in multiple charts.

There are some interesting outliers in the submissions, which are intriguing to look at on their own.

So don’t use this dataset it for your PHD thesis but have fun with it. Here are some insights I found.

I would have never guessed that the number #2 Function was “If”!

I used  a couple of different ranking formulas throughout the analysis, but If() comes out a solid number #2 no matter how you look at it.

This led to another question (there’s that Data Discovery thing): Is there a relationship between the use if Set Analysis and the use of If()?  Not surprisingly, there is a strong correlation.

I’m of the mind that Set Analysis can be overdone and make an app difficult to maintain, vs building function into the data model. I don’t have an exact figure, but I frequently suggest to students that a max of 30% expressions with SA feels about right. It’s a really rough number meant to illustrate the concept that good data modeling is core.

Let’s assume that Swedes are excellent QV developers. There were three submissions from Sweden:

They look pretty good to me. There were submissions from experienced developers whose work I was familiar with and their SA usage tended to fall in that 30%-40% range as well.

What about Objects? I found a couple of surprises there.

I was surprised that Text Object beat out List Box for the top spot. But the biggest surprise to me was that Button made it in the top 5. I’d love to know what all those buttons are for. If you assume that many people use Text Objects with Actions as a Button substitute,  there are even more “Buttons” out there.

There is lots to explore in the data. The analysis has popup and drill down detail so explore with your mouse. And of course create your own visualizations and post your insights here.

Sites that submitted data can select themselves for comparison by selecting the unique submitted QVD name.

I will update the Survey Results app when I receive more data and of course, when you point out my errors J

-Rob

I was recently working with a customer developing training curriculum and the question was asked “what expression functions do  developers use most”. I thought it was a question worth getting a specific answer to. So I’m creating a community project to gather data on what QV functions and features people use.

Please help out by downloading this QVW and following the instructions in the file to scan  your installation’s QVWs. You only need to configure a file path and press reload. The scanner will gather anonymous information and produce a QVD that should be emailed to me per the instructions in the file.

• The scanner runs fast — about 60 QVWs per minute or better.
• The survey QVD contains only counts of function usage — such as how many time sum() is used.
• No private information is included in the survey QVD that you return to me. There are no qvw names, no userids, no expressions no fieldnames or data.
• I will make the collected data freely available to the community for analysis.
• I prefer that you run against your production or QA, just to eliminate the extra copies and backups that appear in a dev environment. If you can’t scan production, please go for the cleanest directories possible.

Why should you take the time to help me on this?

1. You may gain some insights from the data.

2. I help you out with free tools like Document Analyzer, QVPR Analysis and the QlikViewCookbook. Time to return the favor.

Thanks in advance!

-Rob

Version 10 of the QlikView Components scripting library added a number of new subroutines and functions when  Matt Fryer  merged his QVACB library into QVC.

Check out some of the Excel-like functions Matt has added;  NVL, DateDiff, InRange, Days360.  Also new workhorse routines like segmenting QVDs, emptying QVDs and loading Icons.

Along with Matt’s code, we also get Matt on the QVC team ! Matt brings fresh ideas and significant technical talent to the QVC project. You  may recognize Matt as author of the QV syntax plugins for Notepad++, UltraEdit and WordPress.  I’m pleased to welcome Matt as co-administrator and contributor to the QVC project.

The free open source QlikView Components (QVC) library can decrease the time you spend on scripting, improve the quality of your script. and let you share and leverage the experience of QV developers worldwide. If you have questions about QVC, use the QVC Forum here.

-Rob