Living QlikView

Top 5 Little Known QlikView Shortcuts

noreply@blogger.com (Anonymous) — Tue, 01 Jul 2014 10:00:00 +0000

In this post, I wanted to highlight a few little-known QlikView keyboard shortcuts. Everybody is familiar with Ctrl-E to edit script or Ctrl-R to reload our application. But lets take a look at a few shortcuts that you might not know about that provide some quick and cool functionality. We will do this in reverse order for dramatic effect ;)

5. Move locked objects: Alt-Drag

If you ever lock down your objects (Properties --> Layout --> Uncheck Allow Move/Size) then you know that you have locked their movement even for you, the developer. Instead of checking this option again for all your objects, you can simply hold down Alt and drag your object wherever it is needed.

4. Comment & Uncomment: Ctrl-K-C / Ctrl-K-U

I am a fan of comments both in the script and in front end expressions. These comments can help the next developer figure out why you did something, or far more likely, remind YOURSELF of what YOU did 3 weeks ago in this application. Most people are familiar with highlighting text, right clicking and selecting comment or uncomment. But the keyboard shortcut Ctrl-K-C will comment highlighted lines and Ctrl-K-U will uncomment the selection.

3. Activate previous or next sheet: F6 / F7

If you are progressing sequentially through the sheets of an application, F7 will save you a click as you proceed rightward from one sheet to the next. F6 will move you backwards or right to left.

2. Object properties: Alt-Enter

Probably one of the most frequent operations is right clicking on an object and selecting properties. There's a shortcut for that. You still must activate the object with a click, but then Alt-Enter will get you to the properties. That's a time-saver for sure. If no object is selected it will invoke the sheet properties similar to if you right click in open space and select properties.

1. Generate sample data: Ctrl-Q (2x)

Maybe not as usable as the previous, but this one takes the top spot for sheer cool factor. Go to the script editor, and tap Ctrl-Q twice. This will generate some scripting that, when reloaded, will create three tables of associated sample data for you to test with. The script requires no outside files to run and will generate about 2,000 rows of transactions for you with a couple associated dimension tables.

Please feel free to gloat if you knew these already. Or I would be happy to hear about any other really cool shortcuts you have uncovered in your trials.

Everything in its Place

noreply@blogger.com (Anonymous) — Tue, 24 Jun 2014 10:00:00 +0000

The Problem

“Is there an easy way to fully expand and fully collapse my pivot chart?” or its cousin,“Can I just press a button to get the pivot chart back to a default?”

These are relatively frequent questions that I never had fully satisfactory answers for.

Before we get into the tip, I used to recommend Right Click à Collapse All or Right Click à Collapse Dimension Columns to collapse the chart and Right Click à Expand All to expand columns (one-by-one).

That solution is not great because first, if you are expanding the chart all the way out, you still have to do it one column at a time. But the biggest problem presents once you pivot a dimension horizontally. At that point, you cannot collapse the pivoted column without first un-pivoting the column.

Solution – The Layout Bookmark

The solution to this is actually pretty neat. It involves a special kind of bookmark – The Layout Bookmark. Our example will simply involve a collapsed state and a fully expanded and pivoted year column state.

First create a pivot chart and manipulate the columns so that none are pivoted and all are collapsed.

Then we simply create a document bookmark Bookmarks à Add Bookmarks as follows.

Notice that we have unchecked “Include Selections in Bookmark”. We want to be able to invoke this bookmark without affecting the current selections. The only thing this bookmark will change will be the “layout state” of the application.

Next, expand out all the columns and then pivot the year column horizontally.

Create another layout bookmark.

Now we need to create a couple buttons that will simply invoke each of these layout bookmarks. But first, we need to know the id’s. Go to Bookmarks à More…

There you can see that the id’s for these bookmarks are BM01 and BM02. Take note of them.

Finally, create the buttons.

You can name the button whatever you want and then for the button action, use Bookmark à Apply Bookmark along with the id for the bookmark you with to trigger.

Here is the finished product. Hitting the buttons will expand or collapse the chart to the desired setting.

Notes

There are some complications to be aware of when creating layout bookmarks.

· The layout state affects the entire sheet. When you create a layout bookmark, it is taking a snapshot of the entire sheet layout, not just the pivot chart you are concerned with. This includes minimize/restored settings and cyclic dimensions for all objects within the sheet.

· The layout state activates the sheet. This means that if you put this button on a different sheet, it will activate the sheet the bookmark was created on. This could be good or bad but is important to understand for your use-case.

· Layout states are hidden in containers. If your charts are in a container, the layout state for objects within the container will not be saved.

Conclusion

Some of the other things that might be done using layout bookmarks.

· Default sheet state

· Set a cyclic dimension to be shown

· Set a grouped expression to be shown

· Set restored or minimized objects on a sheet

· Set which object is shown within a single style container

I would love to hear any other ideas you have for this concept or other feedback. Happy Qliking.

Easy Way to Collect QVD Stats

noreply@blogger.com (Anonymous) — Wed, 18 Jun 2014 10:00:00 +0000

Collecting Stats Helps Debugging and Alerts You to Problems

Most organizations will use QVD Generators to build out QVD data layers suitable for QlikView’s consumption. There is generally no need to create any visualizations in these QVD Generators except for maybe some validation tables and some QVD statistics. Today we will focus on an easy way to gather those statistics. The statistics I generally gather help me answer questions regarding the output QVDs.

· Which QVDs were written with this application?

· How long did it take to create this QVD?

· How many rows were written?

· How many fields were in each table?

Obviously, being able to easily answer these questions helps us better understand and serve the BI enterprise. It also provides a helpful tool for debugging logical errors in our QlikView scripts.

And because we actually take this statistics table and write it to QVD as well, we can use it in later loads as needed to create a data source lineage for a particular application. Another benefit of creating this statistics QVD(s) is that I can drop all the tables that created the QVDs while still retaining the statistics. This leaves the application light and quick to save.

Here is an example of the front end to show you what kind of data will be collected and displayed. Of course, the stats you collect and present are really up to you, the developer.

How to set up the collection

I use blocks of code that can be basically put into any script and adjusted as needed. We basically have four chunks of code that get deposited into our script.

1. Set up any needed global variables and set up the statistics table structure.

2. Set up a sub-routine that creates the stat table.

3. Change the start time and table name each time we go to a new QVD.

4. Call the subroutine.

Set up any needed global variables and set up the statistics table structure

First, create any variables that will need to be collected in your statistics or displayed in your front end. For this example, we are using a variable to determine my directory, the name of my environment, the source, division and the path for my statistics QVD. These two chunks of script should be inserted towards the beginning of the script before any loading occurs.

//dev
set vDir = 'C:\Users\Aaron\Documents\QlikView Apps\Blog Apps\Statistics Collection';
set vEnv = 'Dev';
//prod
//set vDir = 'D:\QlikView Documents\Production\Sales';

// Set variables for qvd naming
set vDivision= 'QAP';
set vSource = 'BAAN';

LET StatisticsPath = '$(vDir)\ETL_Statistics_$(vDivision)_$(vSource).qvd';

We also will look for an existing statistics QVD and if one is not found, we will create it.

IF FileSize('$(StatisticsPath)') > 0 THEN // The Statistics QVD already exists.
ELSE // The Statistics QVD needs to be created

Statistics:
LOAD * INLINE [
    TableName, Division, Source, ReloadedDate, StartTime, EndTime, Duration, Records, Fields
    , ];

STORE Statistics INTO $(StatisticsPath)(qvd);
DROP TABLE Statistics;
END IF

Set up a sub-routine that creates the stat table

Next we create a subroutine that appends the statistics each time we load new data into a QVD. This script records all of the needed data from whatever load has just occurred, adds it to the existing statistics records and then saves the whole thing back to QVD. This script gets inserted just below the prior set up stuff and will be called at various times later in the script.

sub Stats

     //   Call this script between qvd store and drop table commands to gather statistics for the qvd.
     //   There must be a vStartTime and a vTable variable
     LET vEndTime = NOW();
     LET vRecords = NoOfRows('$(vTable)');
     LET vFields = NoOfFields('$(vTable)');

     Statistics:
     LOAD * FROM $(StatisticsPath)(qvd)   WHERE TableName <> '$(vTable)';
     JOIN (Statistics)
     LOAD DISTINCT '$(vTable)' AS TableName, '$(vDivision)' AS Division, '$(vSource)' as Source, DATE(NOW()) AS ReloadedDate, '$(vStartTime)' AS StartTime, '$(vEndTime)' AS EndTime,
           INTERVAL(NUM('$(vEndTime)') - NUM('$(vStartTime)'),'hh:mm:ss') AS Duration, $(vRecords) AS Records, $(vFields) as Fields
     RESIDENT Statistics;
     STORE Statistics INTO $(StatisticsPath)(qvd);
     DROP TABLE Statistics;

end sub

Change the start time and table name each time we go to a new QVD

So now we go about the business of loading data from our data sources and creating QVDs. The basic idea is that we will need to establish some variables at the beginning of each load and then run the subroutine at the end of each load. Using this approach we can collect statistics for every QVD that gets created. Start by adding this script to the top of each data loading section. Note the vTable variable could also be used within your load statements to name your table.

// Append this script to the start of each qvd creation.
LET vStartTime = NOW();
SET vTable = 'ETL_Sales_Header';

Call the subroutine

Finally, we can call the statistics subroutine that will record our ending variables and append the QVD. This simple code will run the routine we created in the second step.

// call subroutine to gather statistics for the qvd.
call Stats;

Place this code after we store each data QVD. One thing to note is that you should wait to drop the data QVD until after this subroutine runs or simply drop them all at the end of your script.

Thoughts?

There are many ways we can simplify the data we are collecting or expand the data. Hopefully this structure is clear and economical enough for you to adjust to your needs. The example I provided is simply what I use to complete this task and I tweak the code depending on the situation.

As always, I hope you find this valuable. Please let me know what you think. And if you accomplish statistics collection differently or can improve upon this, please participate in the discussion.

6 Pictures Only QlikView Developers will Understand

noreply@blogger.com (Anonymous) — Tue, 03 Jun 2014 11:45:00 +0000

Gurus of their respective fields

We all talk a big game with our complex table structures and fancy visualizations. But once in a while, it is OK to let our hair down (for those of us that have some) and have some fun at our own expense. I thought it would be a nice change of pace to offer up a few examples that might relate to our collective QlikView development experiences. See if you can guess what QlikView concepts the pictures below represent. I went ahead and put the caption text in white so highlighting the caption should give you the answers (might not work?). The answers were also added at the bottom of the post. Enjoy!

Concatenated Keys

Link Table

Synthetic Key

Section Access

Reload

Cycle Group

Here are the answers in order:
1. Concatenated Keys
2. Link Table
3. Synthetic Key
4. Section Access
5. Reload
6. Cycle Group

Thanks to DV over at QlikShare for the inspiration for this post.
As usual, your comments and shares are much appreciated.

Let's Do Something Cool with Color

noreply@blogger.com (Anonymous) — Tue, 27 May 2014 12:57:00 +0000

Script-Side ColorMix – Subcategory Color Assignments

This exercise came from something I did for my personal use, but I think the principal can be applied to business cases as well. You can download the example here.

I have long used the ColorMix wizard in QlikView for heat map charts. Using this function QlikView can assign colors to values in your chart based on its position in the range of all values. This is an immediately apparent use-case but I wanted to do something a little different.

I have a regular fitness program where I mix up different types of activities throughout the week. In addition to the main activities, I want to be able to visually separate sub activities from each other while still maintaining them as activity groups. Here are screen shots of the Activity and the Activity SubClass broken out to show you what I mean:

Left - By Activity Right - By Activity SubClass

So my P90X programs will always be some shade of gray. My Insanity programs will all be some shade of Orange, etc. The front-side ColorMix function is great, but it would be very difficult to assign these in the front-end especially considering the grouped dimension and varying numbers of SubClass groups as time goes on.

The idea is that when only the Activities are shown, I want to explicitly assign the main colors. But when we break these out into Activity SubClass, I want to dynamically assign a shade of that Activity color to each of the Activity SubClass values within it. I realized quickly that the best way to do this was to utilize ColorMix in the script.

The first step is to decide what colors should be assigned to each category. Create a spreadsheet and give each Activity an RGB value. You must also decide what will be the range of colors that will be used to create the Activity SubClass colors. In this case, the Activity list will need to be maintained, but the SubClass values can vary because the actual RGB values for each distinct SubClass will be assigned at reload time.

	Activity Color			Top Color			Low Color
Activity	R	G	B	RH	GH	BH	RL	GL	BL
Insanity	255	151	16	255	102	0	255	224	185
P90X	84	75	71	84	75	71	233	231	228
Hiking	212	250	0	98	116	0	230	254	99
Running	34	84	158	34	84	158	118	154	207
Biking	130	27	159	130	27	159	186	112	207
Elliptical	255	79	79	255	79	79	255	79	79

Next you must create the script that will load these up and create the structure that will facilitate the ColorMix operation.

Create a table with the distinct Activity SubClasses creating a key.

// load distinct keys
TempColorMix:
load distinct ActivityKey, Activity as Act, [Activity SubClass] as ActSub, ActSubSort
resident ActivityLog
where not isnull(ActivityKey)
order by ActSubSort;

Create a counter row that resets for each major Activity.

// add the subrow row numbers for each activity
// must be done in separate step due to ordering and distinct features.
Temp2ColorMix:
load ActivityKey, Act, ActSub,
if(Act<>peek('Act'),1,peek('SubRow')+1) as SubRow
resident TempColorMix
order by ActSubSort;
drop table TempColorMix;

Now find the low and high value for each activity. This will give the ColorMix function the ranges to create colors for.

// find the max and min row number for each activity
left join (Temp2ColorMix)
load min(SubRow) as SubMin, max(SubRow) as SubMax, Act
resident Temp2ColorMix
group by Act;

Add in the actual color ranges we had set up in the spreadsheet.

// join in the guide colors
left join (Temp2ColorMix)
LOAD Activity as Act,
     argb(255,R,G,B) as ColorAct,
     RH, GH, BH,
     RL, GL, BL
FROM Data\Workouts.xlsx(ooxml, embedded labels, header is 1 lines, table is ColorMix);

Note that you can use a standard color dispersion or an enhanced color dispersion that results in a more distinguishable set of colors. I put both examples in the sample script and just commented out the standard one.

// now we can get the color for each Activity SubClass and create final table
ColorMix:
load ActivityKey, ColorAct,
//if(SubMax=1,ColorAct,colormix1((SubRow-SubMin)/(SubMax-SubMin),rgb(RH,GH,BH),rgb(RL,GL,BL))) as ColorSub // standard color dispersion
if(SubMax=1,ColorAct,colormix1((1+sign(2*(SubRow-SubMin)/(SubMax-SubMin)-1)*sqrt(fabs((2*(SubRow-SubMin)/(SubMax-SubMin)-1))))/2,rgb(RH,GH,BH),rgb(RL,GL,BL))) as ColorSub // enhanced color dispersion (less collision in the middle spectrum)
resident Temp2ColorMix
order by Act, ActSub;
drop table Temp2ColorMix;

After reloading, we can focus on visualization. Create a variable expression to assign our colors. We used $(vColorPick).

if(getcurrentfield(ActivityGroup)='Activity',ColorAct,
if(count(distinct [Activity SubClass])=1,ColorSub,rgb(154,151,170)))

This variable can be called in the Background Color attribute for the expression in any chart. If you need help with Background Colors in Expression Attributes reference this article. We added an else statement to take care of any values we neglected to assign in our spreadsheet.

So hopefully this has value for you. I have posted the spreadsheet and qvw here. Feel free to use it to learn more about this method OR obviously you could use it to keep your own workout log.

Is this valuable to you? Then please share.
Do you see other use cases or a better way to do this? Then please comment.

To Calculate or Not to Calculate

noreply@blogger.com (Anonymous) — Wed, 21 May 2014 15:00:00 +0000

My buddy and I recently were having an issue with a QV app in that it was taking up huge amounts of RAM on the server. Part of the reason was that the app was data-heavy and not well optimized. But we were also concerned about sheets that had many, MANY objects on them. The developer had put these objects into containers. So started a heated debate on whether the objects not being displayed in a container were being calculated or not.

Although I always assumed that an object hidden by any means does not take up resources, after viewing a few posts in the community, I was starting to doubt myself.

So I set up a little experiment. I developed a set of 1 million random values tied to a list of 1 million addresses and a calendar to try to closely mimic a true QV app. Then I added 2 list boxes, 3 straight tables and a pivot chart to the display. I then copied the app three times and adjusted so I ended up with the following:

Objects exposed
Objects minimized
Objects in a container
Objects hidden

I then created four memory statistics files and ran them through the QlikView Optimizer. (Settings --> Document Properties --> General --> Memory Statistics) The results were a relief.

It appears that as far as the sheet object resources go, there is very minimal amount of RAM used in either the hidden state or the minimized state. The container ram is relatively higher than zero only because one of the tables is visible in the object at any one time so that is the only object taking up space. And even more encouraging, you can see that calculation time (or CPU cycles) are only utilized for the original (all exposed objects) application and no calculation time registers at all for the other apps, including the containerized version.

This is good news because ANY of these methods that you choose to hide objects from user view will make efficient use of server resources. So as a general rule, if you can't see the object, it is not using resources.

This experiment could have holes. If you have evidence refuting this or clarification that we should be aware of, please leave a comment.

Couron Data Solutions is Back Online

noreply@blogger.com (Anonymous) — Fri, 16 May 2014 12:58:00 +0000

My consulting site is back online Couron Data Solutions. After going through ordeals including uploaded malware, no-backup (my fault) and the lack of time, I have finally got my stuff together and published the new site.

For the Living QlikView blog, this means that the download links that are scattered throughout the various articles here are now back to working again. I thank everyone who left a comment to this point and I am sorry that it took so long to get the links back up.

If you have any feedback on the site (positive and especially negative), please feel free to comment or contact me. I am more of an expert in QlikView than web development, but attempting to close the gap.

And look for the content here to continue. I have lots of ideas.

Best regards,
Aaron Couron
LivingQlikView
CouronDataSolutions

Where was that article I read about the ColorMix function?

noreply@blogger.com (Anonymous) — Wed, 14 May 2014 22:22:00 +0000

A great resource was put up by Steve Dark of Quick Intelligence fame. AskQV aggregates curated QlikView blog articles into a single location.

Why would you want this, you might ask? I, myself, already include most of these in my RSS reader. But this site is cool because you can search the blog rolls for specific topics like “Set Analysis” for example or “Incremental Load”. Are the wheels turning yet?

The site also functions as a news feed and will give you the latest articles form the curated blog collection.

Finally, it will also give you contact information (Twitter, QV Community and LinkedIn) for each author.

So check it out. I think it is a worthwhile tool to help consolidate my "technical notes". I will never again have to ask “Now, where was that article I read about the colormix function?”

A Review - QlikView 11 for Developers

noreply@blogger.com (Anonymous) — Mon, 12 May 2014 16:31:00 +0000

I am asked frequently if there is a book I would recommend to help a beginner better learn QlikView. Up until the last year or so, that was a difficult question to answer because there definitely was a void in this area. But recently I have been recommending a guide that fills this void quite nicely.

QlikView 11 for Developers is written by Miguel Garcia and Barry Harmsen. Both are notable (dare I say famous) BI consultants that have been in the QV space for many years. The book weighs in at 534 pages.

We follow the case of HighCloud Airlines throughout the text. Users are also encouraged to download the example material so we can also develop the solution alongside the text.

The chapters are arranged just as we would normally develop a Proof of Concept (POC) so the rhythm is natural and iterative in approach. This allows Miguel and Barry to cover a broad range of topics ranging from data modelling down to the Class function and every topic in-between. This does mean that the guide is not necessarily organized in a formal way like a reference manual or “encyclopedia”. But by following the case of HighCloud Airlines in a natural progression, it makes the material more readable and realistic. The back-end index will help anybody who wants to reference a specific topic.

I read it a little bit ago and put it away and found myself digging it back out on multiple occasions, so now I just keep it near my workstation in case I need to reference something.

Although QlikView is relatively easy to learn compared with other BI tools, it is still hard! And because it is constantly evolving, ongoing training is also necessary. A book like this was long overdue.

I think QlikView 11 for Developers is a must have for any serious developer. Even an old pro will pick up a few tips and be able to refer to it as a reference (I know I have). And for those of us just starting out in the QlikView world, this will be a handy way to jumpstart the process. It can be picked up from Amazon .

Under Construction

noreply@blogger.com (Anonymous) — Fri, 09 May 2014 13:10:00 +0000

My main consulting website - CouronDataSolutions is currently down due to some sort of malware corruption. I am in the process of changing my hosting platform and developing a new site.

Because of this, the downloadable example material that support this blog are not currently available.

I anticipate it will be another week or two before I relaunch the site.

So I apologize about the current issue. I plan on having a positive relaunch and adding more material to this blog of course that I admit has been somewhat neglected as of late. I have a whole list of learnings and topics to cover and am also excited about QlikView Next, which I have seen in its beta form.

See you soon.

Regards,
Aaron Couron

Alternate States Use-Cases

noreply@blogger.com (Anonymous) — Mon, 20 Jan 2014 21:24:00 +0000

Not to be confused with the 1980 movie starring William Hurt (Altered States)

I was recently invited to do a presentation on Alternate States and it's use-cases. So I created an example application and thought it should be shared here as well. This is a feature that was added in the first version of QV11. It is extremely powerful especially for Comparative Analysis.

You can download the sample application here.

The concept of Alternate States is that we can create "copies" of the data model within the QlikView application. These copies can then be referenced at the sheet, object or expression level. It's like creating parallel universes where we can look at each universe side-by-side, making comparisons. If the developer uses this concept effectively, users can create truly dynamic side-by-side comparisons.

Sheet Isolation
One use-case of this feature is to isolate sheets from each other. There are times (infrequently) when users would want one sheet's selections to not propagate to the other sheets. We can simply create a new state in the document and assign the sheet to the new state. All objects created in that sheet will only respond to selections made in that state and will ignore selections in the default or other states.

Go to Settings --> Document Properties(ctrl-alt-d) --> General --> Alternate States
Select add and then name your new state.

Now within the sheet properties (ctrl-alt-s), simply pull down the Alternate State drop box and choose the new state.

Any objects created within this sheet will by default respond only to selections in this state, thereby isolating this page from the rest of your application. This really occurs because when an object is created on a sheet it will be assigned the <inherited> state. This means that the object will simply take on the state of the sheet.

So continuing on with this method, you could conceivably create a QlikView application with several sheets that are all completely isolated from each other. Simply create a new state for each sheet.

Comparative Analysis

The second use-case I have found for using Alternate States is when you want to enable a form of Comparative Analysis. This allows to basically set up two list boxes containing the exact same field, but assigned to two different states. Then we can create charts that utilize the selections from those states in expressions. This allows to compare a mix of products against a mix of other products, for example. This is extremely powerful as you can compare one product to several other products at the same time, or one grouping of products compared to another grouping of products.

The sample application includes two types of expressions to help explain the syntax.

Method 1

The first example on sheet Product Bucket Comparison can be set up by following these steps:

Create two alternate states in Document Properties --> Alternate States. I called mine Group1 and Group2. Leave your sheet in the default state for this example.

Create two list boxes using Product_Long as the dimension. The first list box will be assigned a state of Group1 and the second will be assigned Group2. Note that when making selections in these boxes, they are not reflected in any other object right now, including the current selections box.

Now create a bar chart in the default state with no dimensions. We will add two expressions:

Group 1 Products: SUM({<Product_Long=Group1::Product_Long>}ExtendedAmount)

Group 2 Products: SUM({<Product_Long=Group2::Product_Long>}ExtendedAmount)

Note the use of set analysis in the expressions above. We are referencing the alternate states in the suffix of the modifier. In layman's terms we are asking QlikView to sum up the extended amount, respecting the default state of selections where Product_Long corresponds to the selections for that field in the Group1 state. The "::" allow us to reference and separate a state from the field in the set. This object and the expression still operate in the default state "$" (implied), but will now respect the selection for that field in the other states.

Doing it this way ensures that the other selections on the sheet still operate at a "global" level. So we can filter down to a specific universal criteria and then make selections in the Product list boxes to get our comparison.

Method 2

Now we will, in effect, do the same thing but use a different format of expression. In this second example, the user can select different groups of months and then see the comparisons on the screen. We could build static set analysis to cover frequent comparisons like this year vs. last ytd, but these dynamic kinds of comparisons would be difficult to achieve without Alternate States. Using dates in your comparative analysis is a good use-case as I frequently get asked to have the ability to compare this April with February, or April against the prior three months as an example.

Follow the same steps as above. The only thing that will be different in this example (besides field names and such) will be the syntax in our expressions:

Group 1 Dates: SUM({Group3<Category=$::Category,ProductLine=$::ProductLine,Product=$::Product,

[Product Num]=$::[Product Num],SubCategory=$::SubCategory,LastName=$::LastName,
Gender=$::Gender,City=$::City,State=$::State,Country=$::Country,Occupation=$::Occupation,
[Home Owner]=$::[Home Owner],NumberCarsOwned=$::NumberCarsOwned,NumberChildrenAtHome=$::NumberChildrenAtHome,
YearlyIncome=$::YearlyIncome>}ExtendedAmount)

The first method would actually still be preferred, but I wanted to show you another way of achieving the same result. In this case we have assigned the state in the identifier of the set, and then made exceptions for the "global" list boxes on the page. In this case they will be directed to the selections in the default state for those fields.

Notice in the straight table we have actually calculated the variance between the two sets in a single expression. We did this very simply with: (column(1)/column(2))-1

Conclusion

As far as the two methods go, I find the first method to be easier in most cases where we are doing some sort of comparative analysis. The second would be better if we are disregarding selections in the default state or if there are very few default selections being respected.

Probably the most important thing to remember as a developer is to make sure it is very obvious what the user is supposed to do with these sheets. Use consistent colors when a group is referenced and include text descriptors to help lead users through the experience.

There are probably other use-cases I have not thought of and definitely much more elaborate examples of this. I would love to hear about other use-cases for this feature. Have fun!

Adventure Works Year Update

noreply@blogger.com (Anonymous) — Mon, 13 Jan 2014 23:25:00 +0000

Adventure Works - Our favorite fictional company.

There are many times when we must rely on a sample database to develop our QlikView applications. I use these DB's for POC's, training, for testing a particular technique, creating examples, blog posts and other situations.

My standard "Go To" has got to be the Adventure Works database that comes with MSSQL.

But the problem with ANY sample database is that the data tends to grow stale in regards to the date columns. It seems that too quickly, data that felt so fresh in 2008 doesn't make much sense in 2014 or beyond.

So I developed a QlikView application that will take my favorite sample database and update all the date fields to the maximum year of my choosing. This allows me to get a few more years out of my Adventure Works database without having to make excuses for the age of the data.

This particular application extracts all the tables from AdventureWorksDW2008R2, transforms the date columns in needed tables and then saves all the tables in the schema to the directory of your choice.

Instructions

Download the qvw here
On the first page of script, create a new connection to match your SQL instance.
Adjust the value for vMaxYear to the highest year you want to appear in the sales data.
Adjust the value of vQVDPath to the fully qualified path where you wish the transformed qvd's to be deposited.

Notes

The script is specifically for AdventureWorksDW2008R2. But feel free to adjust to the version you are using or another db.
The DimData table is omitted since I usually create my own calendars in QlikView. All other schema tables are extracted and stored to qvd.

Control Your Date Controls

noreply@blogger.com (Anonymous) — Mon, 29 Apr 2013 12:00:00 +0000

Do your QlikView applications have date controls? Almost all of mine do in one way or another. Most of the time, I have resorted to the traditional Year, Quarter and Month list boxes that we are accustomed to. Something similar to this:

This is fine for many business users but sometimes users have more exacting needs that cannot be selected with the above controls. If a user needs to look at the months of November 2012 and Jan 2013 for example, there is no way to make that selection with the above list boxes. So, we have to add another list box for the Month-Year combinations.

Then if users have need to look at specific quarters or weeks in the same manner, now we have lots of extra list boxes on the screen that we do not likely have room for.

With the addition of containers, we now have an easy way to provide our users with the best of both worlds. The key to this idea is that the user likely does not need to utilize both styles of date controls at the same time. They will need one or the other for any given analysis need. So hiding one set while the other set is active allows us to reuse the screen area.

Using nested grid and single-item containers, you can create a very powerful date control set while, preserving the vital screen real-estate for your real data. You may also incorporate cycle dimensions for a different feel. Lets look at some examples. You can find the qvw here.

In the default view, the user sees the traditional date segment view:

If the user selects Range, they will be presented with the ability to select specific year, quarters or months:

And finally, they can drill down one more step to find specific weeks or dates.

Another option to display the date ranges is to use a list box with a cycle dimension to change the interval type. This gives us a clean look, allows a larger amount of values to be displayed at once, but limits us to one type of date interval at a time.

There are probably other variations of this idea that may be even more effective and helpful for the user. Hopefully you can utilize and improve upon this in your own work.

Comments and feedback always welcome.

Look! I can see my qvw from here.

noreply@blogger.com (Anonymous) — Mon, 15 Apr 2013 12:46:00 +0000

Our friends over at Vizubi, famous for their great NPrinting product, have created a neat little plugin called QlikLook.

Have you ever said to yourself, “Where is that qvw that I did <blank> in?” Which qvw had that trick for establishing a closed hierarchy? Where was that expression with the crazy set analysis in it? Which qvw has that great mini-chart example? Which qvw out of the 200 scattered throughout my local drive is the one that I am looking for?

QlikLook allows you to preview any QlikView document (qvw) in an Outlook or Windows Explorer preview pane.

In addition, you can browse the sheets and make selections in the preview pane. Basically you can use it as if you are using the document in a browser from the access point.

You can find the application here . IT IS FREE. You will be identified through LinkedIn, thus allowing you to download the executable and license. The install is straight forward.

Now, if you enable your preview pane in Outlook (in the View menu) and then select an email that has a qvw attachment, the preview pane will fill with the sheets in that qvw. For Windows Explorer, the preview pane is turned off by default. To turn it on go to Organize à Layout à and enable Preview Pane.

Although there is some lag depending on the size of the document, it seems to respond very well and has helped me scan through documents without opening QlikView (or yet another instance of it).

This is an incredibly convenient and useful tool. I would encourage everyone to check it out.

My Help Sheet Needs Some Help

noreply@blogger.com (Anonymous) — Mon, 01 Apr 2013 16:38:00 +0000

The poor help sheet. It never gets any attention. Yet, it is an incredibly important piece of your user experience that should not be overlooked. Many of us have copy/pasted/adjusted derivatives of this:

Now this is a very old example straight from the QlikView Demo site, but I have used something similar to this for too long, choosing to spend my attention on the other aspects of my design.

The old-school help sheet suffers from several issues.

First, the browser-agnostic environment of today has brought in issues off differing treatment of alignments and kerning. So, if we use a traditional layering of icon text objects on top of text objects with paragraphs of text, you will get differing results depending on the browser being used. Here is an example of a sheet I used that was viewed later in Google Chrome:

Secondly, I have found that the copy/paste method is not scalable. When the product features change, it would be better to manage the help descriptions and icons from a central location that could then be dispersed to all my apps.

Lastly, many environments still use both the AJAX and the IE Plugin client. This creates yet another issue in that the menu icons look completely different for each client. It sure would be convenient if we could tell which client is being used and then display the appropriate icon set.

I have handled these issues for my own development, so I thought I would share J

Help Template

You can find the template and the files I am using to populate it here: help_template_repository.zip

The solution consists of first cropping pictures of all the different icons you wish to use in your help sheet. This was completed with an example document opened first in AJAX and then with the IEPlugin so I could get all the possible icons.

Then, a spreadsheet was created to house the names of all the pictures, id, client, category and description. I also created another worksheet to house my larger chunks of text that would be shown in the help sheet.

Store all of this where your QVWs can reference it.

Then I had to handle the loading of this. I chose to create a txt file that I could manage outside of QlikView that could be referenced with an include statement:

$(Include=c:\qlikview documents\qv production documents\qlikview communal\include\help\help_bundle_load.txt);

Within the txt script, we first have to set variables with the paths of the icon repository and the spreadsheet. I used the full UNC paths since QVWs using it might be scattered about the server.

SET vHelpDataPath = 'C:\QlikView Documents\QV Production Documents\QlikView Communal\Data\Help\Help_Text_Standard.xlsx';
SET vHelpIconPath = 'C:\QlikView Documents\QV Production Documents\QlikView Communal\Graphic\Help\';

Then we load the xls worksheets. To get the actual pictures loaded into the QVW we use the BUNDLE LOAD. This actually embeds the pictures into our documents. Care should be taken with this function to ensure you are not loading a large number or large sized pictures as this would hinder performance.

BUNDLE INFO LOAD Picture_ID, '$(vHelpIconPath)' & [Picture_Name]
RESIDENT Help_Icons;

Lastly, we need to reference all of this data in our help sheet. To load blocks of text, I created text objects with expressions like this:

=Minstring({<Help_ID = {2}>}Help_Text)

For the icons, I chose to create charts to display them, rather than trying to layer each one in between pieces of text. You can create a chart with Picture_ID as the dimension, then Hide that column. I then used two expressions, one to show the icon picture and one for the text descriptor.

Notice the use of ClientPlatform(). This function will return nothing if the user is on the IEPlugin. It will return the name of the browser if the user is on the AJAX client. With this function, we can display only the appropriate set of icons to each user.

The INFO function tells QlikView to display the embedded file associated with the field, rather than displaying the text.

Picture:

=IF(LEN(ClientPlatform())>0,
MINSTRING({<Picture_Category={'Selection'},Client_Type={'AJAX'}>}INFO(Picture_ID)),
MINSTRING({<Picture_Category={'Selection'},Client_Type={'IE'}>}INFO(Picture_ID)))

Text:

=IF(LEN(ClientPlatform())>0,
MINSTRING({<Picture_Category={'Selection'},Client_Type={'AJAX'}>}Picture_Text),
MINSTRING({<Picture_Category={'Selection'},Client_Type={'IE'}>}Picture_Text))

You will have to set the picture expression to Representation: Image and select “Keep Aspect” from the Image Formatting dropdown.

Final Result

I still manually create example charts and controls representative of my data to fill out the rest of the help sheet, but at least the main portions of my help sheet can be easily created and centrally managed.

After adding the rest of my data model and the supporting charts, here is my finished product opened in AJAX and then with the IEPlugin:

I always welcome your comments and suggestions.

Fine Tuning Your Objects - Dimension & Expression Attributes

noreply@blogger.com (Anonymous) — Sun, 02 Sep 2012 19:37:00 +0000

Most of the time, clients simply care about getting their data. And it is sometimes no small effort to transform business requirements (or lack thereof) into a working QlikView application. Sometimes there is no room left in the budget for the finer design points.

But many times, I find that going that extra mile to bring simple and intuitive meaning to the data can make the difference between QlikView being perceived as a lackluster business tool and the “lean forward experience” that we all want it to be (to use the words of Donald Farmer).

Maybe we should highlight the new product line in our bar chart, or maybe we want to see the best and worst performers in a table or I want my forecast as a dotted line and my average trend to be a thinner line.

Now this kind of highlighting is not always intuitive in QlikView. It took me a while to realize the power of these settings and I am still not at the point where I have memorized all the small syntax pieces needed to add these touches. So I thought I would put together a small article to illustrate some examples and also to give myself and others an easy place to reference these codes.

The items we will cover are accessible in the dimensions and expressions tabs of your object properties. If you expand the plus sign in your used dimensions or expressions, you will find the attributes we are discussing. All these settings will require some form of calculated expression to work. Also note that these settings will override any other display settings you have in the object. We will examine these attributes in order.

Dimension Attributes

Dimension attributes only work in straight or pivot tables and will affect dimension columns.

Background Color and Text Color

These two attributes work the same way with one affecting the cell background and the other affecting the font color. In this case, we want to highlight our new product line:

Background Color:

=IF(Product='Bazinga',RGB(100,100,100))

Text Color:

=IF(Product='Bazinga',White())

Text Format

The Marketing Director likes that but wants to go a step further by displaying the text in bold italics. No problem:

Text Format:

=IF(Product='Bazinga','<I><B>')

Note that you can use the following text formats and also that you can use more than one at a time like in our above example.

=’<B>’	Bold
=’<I>’	Italics
=’<U>’	Underline

Expression Attributes

Expression attributes are available in some capacities for all chart types. These attributes affect the expression columns of a chart.

Background Color, Text Color and Text Format

Just like in the dimension attributes, Background and Text color change the colors of values in the data. Text color only has an effect on straight or pivot tables. Background color is adjustable for tables, and several other charts. First, let’s adjust our straight table example again. This time we want to highlight the cells that represent the best and worst performers in their expression columns. We will also adjust the text format:

Background Color:

=IF(RANK(SUM({<IsForecast={0}>}Qty))=1,LightGreen(),
IF(RANK(-SUM({<IsForecast={0}>}Qty))=1,Red()))

Text Color:

=IF(RANK(-SUM({<IsForecast={0}>}Qty))=1,White())

Text Format:

=IF(RANK(SUM({<IsForecast={0}>}Qty))=1,'<B>')

Now let’s look at a bar chart. We will go back to our previous example of highlighting the value that is our featured product type. This is done with the Background Color attribute:

Background Color:

=IF(Product='Bazinga',RGB(0,255,0))

Pie Popout

The Pie Popout attribute is obviously only available when using a pie chart. This will advance one slice of the pie slightly out from the center. Let’s highlight our chosen product line.

Pie Popout:

Product='Bazinga'

Bar Offset

This attribute, when used in a bar chart, will raise a bar off the x-axis by a calculated amount. This could be useful to create gaant charts. For lack of a better example, we will stack each year’s sales on top of the previous total.

I first created an invisible expression called RunningTtl:

IF(RowNo()=1,SUM(Sales),
ABOVE(RunningTtl)+SUM(Sales))

Then I created this expression in the Bar Offset attribute:

IF(RowNo()=1,SUM(Sales),
ABOVE(RunningTtl)+SUM(Sales))-SUM(Sales)

Line Style and Show Value

These attributes are for use in line charts (or combo charts with line expressions). The Line Style attribute changes the line to dotted, for example. Show Value will allow us to selectively choose which points have a textual representation. As stated before, these attribute settings will override any higher level configurations. This time, we would like the sales figures to be represented by a continuous line (default) and the forecast to be displayed as a dotted line. We also have a request to label the current year’s sales with text. In addition, we want an average line that is very thin compared to the sales line:

Line Style (continuous vs. dotted):

=IF(IsForecast=1,'<S3>')

Show Value:

Year=Year(Today())

Line Style (width):

='<W.5>'

Line Style – Type	Description
=’<S1>’	Continuous
=’<S2>’	Dashed
=’<S3>’	Dotted
=’<S4>’	Dotted & Dashed

Line Style – Width	Description
=’<Wn>’	n = .5 – 8 to determine width of the line.

I am sure there are many more compelling uses for these attributes. I believe they can add tremendous value in your visualizations as long as they are used for valid reasons. Take your applications to the next level.

Developing QlikView Apps for the iPad

noreply@blogger.com (Anonymous) — Tue, 01 May 2012 13:08:00 +0000

All observations here are as of QlikView 11 SR1 (11282).

Overview

In my last article I talked about my overall impressions of QlikView on the iPad. To review, I am excited about it as it will revolutionize the way we consume data in business.

But the experience does have some issues that are specific to the iPad. Most of these are very minor and can be thankfully worked around without too much effort. The key with all of this is balance. We want to ensure that the user performance is as optimal as possible regardless of the appliance that will be used to access QlikView.

Ratio

The first area we must focus on is screen ratio. Now it is easy and perfectly OK to perfectly ignore this issue by building a sheet that is very long (or wide) that basically encourages the user to scroll down through the entire page like a blog for example. I have seen examples of this and it works well on the iPad. But if you do not take this approach, then you should strive to create the perfect ratio that encourages an automatic “snap-to-fit” on the iPad. In my experience, that ratio is 1024:628, or my preferred overall resolution of 1320:810 (more on size later). You might need to experiment with this template to reach the perfect balance. You want the snap-to-fit gesture to automatically place the edges of all your objects to be in view.

Ignore the ratio issue with this type of a layout so users assume scrolling down the page:

Respect the iPad screen ration with this style of a layout (not to scale):

Whatever you do, make your choice obvious. If you follow the long approach, it would be wise to have an object or two that crosses the threshold of viewable vs. non-viewable space so that the user can easily observe “there is more to discover” rather than having to consciously look for a window scroll bar.

Size

Size or resolution goes hand-in-hand with ratio. I know when I am developing for the iPad that users will actually be using their desktop/laptops as well as the iPad. I personally find that limiting the overall resolution of an app to 1024:628 pixels makes for a very small footprint on a typical office workstation monitor. Besides, the newest version of the iPad is double that size with a native resolution at 2048:1536. That is actually too large for most office monitors! So I have found my “sweet-spot” at 1320:810. This creates a very presentable window on most monitors while also creating that “snap-to-fit” ratio we talked about on the iPad. An added advantage of more pixels is that when a person zooms with the newer iPad, we will retain a sharper image as we move to a higher overall resolution.

The other issue concerning size, is that the iPad will naturally reduce the height of the tab row with an increase in the overall width of the application when it is fit to the screen. This means that you need to be cognizant of the balance so that the tab row is easily navigatable or not use a tab row at all. Either way, this will affect the “perfect ratio” slightly, so some adjustment might need to take place.

The easiest way I have found to make that adjustment is to place an almost transparent 10x10 text box in the lower right corner of your screen. Of course, you must subtract 10 from the X and Y since the text box consumes 10 pixels both ways. But fine adjustment of this text box and then testing on your access point with an iPad will get you to that perfect fit. And then I just leave that text box there on each sheet so that the iPad knows what to snap to. A user will likely never notice it.

Text in a Text Box

So once we get past the big issues of ratio and size, there are many small inconveniences that have to be worked through. The first is the positioning of text inside a text box. You have probably noticed on various browsers up to this point, that text placement can vary inside a text box. If you have ever placed a text image like an icon on top of a text box with text, you have definitely seen your images “move” in that text frame.

What is actually occurring is that different browsers treat fonts and font sizes slightly differently. So Internet Explorer might fit 150 Arial 10pt characters into a text box while Google Chrome might fit 155 characters into the same width box. Observe the following:

QlikView Desktop:

Google Chrome:

Internet Explorer:

iPad:

The above scenarios illustrate this is not a one-size-fits-all scenario. The other problem with the way the iPad in particular treats text boxes is that regardless of the vertical positioning you select, text will always appear Top justified.

So my approach then is to make all my text boxes top justified. To solve the character spacing issues, the only thing you can do is to purposely decide where line breaks will be. This will minimize the issue, although it does away with the convenience of natural word wrapping.

Font Sizes

When navigating on an iPad, we use our big fat fingers. Our fingers in reality are a poor substitute for the fine control of a mouse when it comes to selecting something. That means we need to make our buttons and our selection areas larger than normal. If a person is expected to click on something, you need to use at least an 11pt font. For chart legends and other displayed text, you can go to 10pt or maybe 9pt. But, keep in mind that the overall resolution of your app will enlarge or shrink the font sizes when the screen is fit to the iPad. I would suggest that after you have settled on the overall resolution, create some list boxes of different sized fonts and try them out.

Objects

Multiboxes – I have heard people say that multiboxes are a no-no for the iPad. I have only gotten in-depth the experience on version 11 and I think they are fine. The only issue is that I would not use grid style multiboxes in any application because the AJAX client renders them as the normal multibox. This is an AJAX issue, not an iPad issue.

Sliders – It is very frustrating using a slider on an iPad in its current form. For the life of me, I can’t get consistent operation no matter what I do. Unless you want users to throw their iPad across the room, don’t use them.

Horizontal Scroll Bars – I have no problem using objects with a vertical scroll bar. Just sliding your finger up and down the left side operates it well. And for that matter, horizontal scroll bars on straight tables and pivot charts work fine too. But for some odd reason, if you create a horizontal scroll on visual charts like line charts or bar charts, it is difficult to engage the scroll control. I have set my charts to continuous or not set a x-axis limit for the most part.

Hover Help – This is kind of obvious. There is no way to hover on a touch device. So, all those fancy help hovers and chart value popups will not be accessible when using an iPad. A nice way to do this for help and definitions is with an “info” button placed on top of an object or somewhere else on the sheet. For values that normally popup, you will need to display expression as text or maybe allow fast change to a straight table.

Right Click – Another obvious problem is that there is no right-click on a touch device. So all the fancy options you get when right clicking an object are not accessible. The easy fix for this is to go to captions and add the “menu” icon. This will allow a user to click on this icon and pull up all the right click options. Of course this might not be a necessity…

Missing Functionality – You might notice many missing features in the iPad AJAX experience compared to the desktop AJAX experience. Here is a list of what you cannot currently do on the iPad:

Right Click Object Menus:

Properties
Copy
Print
Export

AJAX Menu Options:

Selections
Repository
New Sheet Object
Select Fields

So why can I “Send to Excel” on an iPad but I cannot popup current selections? Your guess is as good as mine.

In Summary

Luckily, there are not that many items to consider when balancing an application for multiple device use. Just being aware of the issues will help us create more universal applications.

The iPad and other mobile devices represent a huge potential to get QlikView into the hands of more people in more places. So let’s encourage that movement by creating applications that work well with these new devices.

The iPad and QlikView - A Dynamic Duo

noreply@blogger.com (Anonymous) — Tue, 17 Apr 2012 00:27:00 +0000

(Warning – This will be light on the technical stuff and heavy on subjectivity for a change. I will follow up soon with some more technical information on this topic)

There are many famous duos in American popular culture.

Simon and Garfunkle

Axl and Slash

Batman and Robin

Like all great teams, their value is greater than the sum of their parts. Great teams are almost inseparable (although Paul Simon had a great solo career). Great teams complement each other.

So for the consideration of the Academy, I would like to nominate a new great duo:

The iPad and QlikView

After recently picking up the newest iPad, I was pleasantly surprised by the experience. And I was surprised for a few reasons. First, QlikView is beautiful on the iPad. Secondly, it works well. And lastly, it is FUN.

Of course, every great team has its share of problems. Axl and Slash hate each other today. But I will save the minor issues relating the iPad and QlikView for another post. But I will save any of the minor issues and design workarounds relating to QlikView and the iPad for another, more in-depth, post.

The Beauty of Mobile Data

I never liked the AJAX experience until recently. I am so used to the full desktop client that it is hard to swallow all the flaws that become obvious in the AJAX rendering. With the release of QlikView 11, this has become less of an issue lately.

But I think the essence of why it translates so well into the iPad is the smaller form factor. The screen is big enough to provide ample real estate, while small enough to some flaws in pixel-perfect placement.

And the new retina-display allows for a very sharp image. They “sparkle” off the tablet. The greater resolution also allows for a perfect rendering when zooming.

Elegance in Performance

It is likely cliché at this point to talk about how the iPad “just works”. QlikView R&D must have been taking notes, because when you open a document on the iPad, it just works. Having an interface you can touch is a natural fit for a product like QlikView. QlikView has taken advantage of many of the inherent gestures and other methods for getting around on iOS. The interface is intuitive and fast.

Snapping to fit works very well, generally adjusting the resolution to the smaller of the length or width in relation to the aspect ratio. Zoom is equally easy and intuitive. Traditional desktop users will miss the right click and lack of hover, but good design will make up for these issues.

Having Fun with QlikView Again

It is likely a combination of the mobility of the hardware and the excellence in execution from both parties. I am having fun using QlikView again.

I have found myself spending hours on the couch at night, analyzing my data on the iPad. Tapping through selections and seeing the changes on various tabs of my documents is actually fun again. It reminds of when I first started using QlikView. And the fact is, I can do this analysis on the couch in front of the TV (although the TV is mad because I am ignoring it). The iPad has reinvigorated my excitement for data analysis and application design.

The Future of this Great Team

The wave is coming. Organizations are starting to see the value of this new hardware and QlikView has smartly put itself in a perfect position to reap some of those rewards.

I often see commercials for iPads and other tablets where they gush about how useful the tablet is for business. Invariably there are screenshots of some charts and graphs on the display. QlikView fits the bill perfectly. It is the quintessential mobile BI app. With this new mobility we can discuss scorecards with vendors, industry statistics with clients, and widget production data on the factory floor. It is definitely coming to a user, client, company, enterprise near you.

Visualize This - The Missing Bridge Between Good and Great Design

noreply@blogger.com (Anonymous) — Thu, 05 Apr 2012 18:23:00 +0000

Here is a video produced by QlikView that makes me want to redo every QlikView document I have ever created. But are these slick visualizations within a typical designer's reach? Does QlikView make this type of functionality easy?

Great Design

The visualizations used in the various examples are pretty and effective and reinforce the idea that GOOD design is time intensive and requires a different set of skills than the backend development piece.

People tend to spend a lot of time on the data because it is perceived to be more difficult AND it is either right, or wrong. There is no subjectivity.

But the quality of User Interface design rests on a continuum from bad to good. You can put in some minimal effort and get a document that functions. But great design as exemplified in this video takes planning, a thorough understanding of design principles and technical skill. And, I believe it is currently a scale of diminishing return. To go from bad design to mediocre design is relatively easy. Going from mediocre to good is a larger effort and moving toward great design requires quite a bit more (read $$$).

Can I Create These Visualizations?

The gripe I have is I wish QV actually came with easy ways to create some of these:

Many of the graphics effects you see there require photoshop or similar software, along with that skill set. Unless you are able to "borrow" graphics from the internet that meet your exact need. The gradient dividers and arrow shaped text boxes for example must be created or found outside of QlikView. QlikView could use a "shapes" library to do arrow shaped text boxes and that sort of thing. As an example, the arrow shown (4:35) does not exist in QlikView. It must be created or obtained elsewhere and used as an image in a text box. There should be a way to do these types of simple representations without additional software.

QlikView could use a "legend" object that could link to various objects and could have customizable display options. Legends are extremely limited today in QlikView. The ability to customize the graphic column of the legend or to spread the legend horizontally simply do not exist, not to mention the ability to move the legend completely off the chart to another area of the page.

Also, the last piece regarding the dimming or highlighting of objects based on selections (6:08) is tricky and requires many similar or repeated calculations at different levels within an object. Some of this can be mitigated with variable expressions, but it would be nice if settings existed to do this kind of stuff automatically.

So the bottom line is "Yes" I can create this quality of design. But are clients willing to spend the extra dollars required to get design to this level? I would generally say "No". Maybe if QlikView can create some of the design tool improvements listed above my answer to that question could more often be "Yes"!

My Sentiments on Sentiment Analysis

noreply@blogger.com (Anonymous) — Fri, 30 Mar 2012 04:21:00 +0000

My Sentiments on Sentiment Analysis

I have recently started working with sentiment analysis. This is a new and interesting field in business intelligence and big data. Sentiment providers promise to be able to quantify how users “feel” about your organization through the interpretation of comments, tweets or other content provided to you.

There are a few limitations of this type of automated analysis. Firstly, a computer algorithm has difficulty with figurative speech, sarcasm and the “lingo” of the day. Surely, a computer will believe “sick” is negative, but what will it think of “that snowboard is sick, dude”?

Secondly, the brevity of many of the social media forums leads to less accuracy in scores. A computer can glean hundreds of adjectives and verbs and rate them in an essay, but when you are limited to a Tweet of 140 characters, there is not much for the computer to score.

Companies like Repustate give you tools to monitor your online sentiment from your Twitter or Facebook feed for example. They also will allow you to access their scores through an API so that you can bring sentiment analysis into your own application.

So I did this with QlikView using the power of QVSource and Repustate.com who provides 50,000 free calls a month. I will not get into the details of how to do this because it is actually pretty easy. Instead I want to examine how accurate or “usable” the resulting information looks to be.

Repustate will take the text string you provide and answer back with a number. A zero or close to zero number equates to a neutral sentiment. A positive number obviously indicates a happy or encouraging sentiment and a negative number would be negative emotion. Obviously, words like “amazing” and “encouraging” should trigger positive scores and phrases like “pissed off” or “horrible service” should warrant a negative score.

So let’s first look at string length.

1: Horrible Service

Score = 0

2: I received horrible service from a company that shall remain nameless.

Score = -1

If you just say “horrible service”, Repustate gives this a neutral score of zero. But if you lengthen the string to “I received horrible service from a company that shall remain nameless”, the score comes back as -1. So even when reducing to the presumably negative words, string length is a huge factor.

Let’s look at some other anecdotal examples. Consider the following two tweets. Which one is positive and which is negative?

1: @_____ I agree that the #personalcloud will replace the PC but I am wondering how comfortable people will be with this concept. Security?

2: @_____ I could not agree more! I am far from technical yet #QlikView allows me to create my own dashboards. #empowers

Oddly, example one received a positive 3.00, on the upper end of our sample while the second example received a negative 3.00, on the lower end of our sample. This seems a little contrary to my judgement. I would say the first example is slightly negative and the second is quite positive.

Consider this third example:

#BioPharma Companies make faster and smarter decisions using #BI http://conta.cc/hPG0dm

You would think this would result in a positive sentiment due to the phrase “faster and smarter” but it actually resulted in a neutral score of zero.

Tweets are limited to 140 characters and probably lots of these characters are likely rendered useless with the the hashtags, usernames and urls. So let’s take a look at some longer strings and see if we can get some more accurate values. Both quotes are by Thomas Jefferson.

1: We hold these truths to be self-evident: that all men are created equal; that they are endowed by their Creator with certain unalienable rights; that among these are life, liberty, and the pursuit of happiness.

Score = 1.00

2: Enlighten the people generally, and tyranny and oppressions of body and mind will vanish like evil spirits at the dawn of day.

Score = -1.00

So the first score makes sense, but I think a human would interpret the second example to be positive in sentiment.

Let’s look at a slightly longer quote about happiness from Sharon Salzberg:

As I go through all kinds of feelings and experiences in my journey through life -- delight, surprise, chagrin, dismay -- I hold this question as a guiding light: "What do I really need right now to be happy?" What I come to over and over again is that only qualities as vast and deep as love, connection and kindness will really make me happy in any sort of enduring way.

Score = -.103

So the computer rates a quote exactly about happiness with a slightly negative score. I am not sure I understand the logic there.

Try this made up text string:

This was supposed to be an amazing and beneficial seminar but I should have known better.

Score = 1.00

You can trick the algorithm fairly easily.

These examples were picked because they illustrate some issues with automatic sentiment analysis. The seemingly odd scoring might not be indicative of the accuracy in a larger set.

But in general, I think we need to be cautious on how much value we attach to sentiment analysis. The good news is, the algorithms will definitely improve over the next few years as more organizations begin to look at this accumulation of social media content that we can clearly call big data. How do we analyze this data and create an understanding that will help us make decisions? We are starting this conversation now and I am sure it will only grow in importance over the next few years.

Sometimes Fat is Better

noreply@blogger.com (Anonymous) — Mon, 13 Feb 2012 14:35:00 +0000

Your data model makes a big difference in the performance of your QlikView application. Of course, if you have been working with QlikView for any length of time that is pretty obvious. Here is a situation you might come across I thought I would share.

The client was trying to monitor costs for production items on a per unit basis as they travelled through the various cost centers. So there are several hundred products, and a few dozen cost centers, with each combination having a per unit cost. Then, it should be a fairly easy exercise to find the extended costs as units are sold, “slicing and dicing” by things like customers, dates, cost centers and other groupings.

Being fairly rational, I figured out a few ways to do this:

Product	Cost Center	Cost per Unit
A	Assembly	0.0233
A	Packing	0.0324
A	Q&A	0.0133
A	Storage	0.0544
B	Assembly	0.0766
B	Packing	0.0222
B	Q&A	0.0652
B	Storage	0.0143

This is the “thin” table approach. Load all the costs into one column and then use set analysis in your expressions to display each cost center like: SUM({<[Cost Center]={Assembly}>} [Cost per Unit] * [Units Sold]).

Product	Assembly	Packing	Q&A	Storage
A	0.0233	0.03424	0.0133	0.0544
B	0.0766	0.0222	0.0652	0.0143

Here is the “fat” table approach. Create a cross table, with a column for each cost center. This results in a fairly wide fact table in your data model but no need for set analysis in your expressions as you would reference each column independently: SUM([Cost per Unit] * [Units Sold]).

Now I have always been told as rule of thumb, “Create narrow, long fact tables” and “when you have two like facts, concatenate them together, rather than having separate tables with a link”.

Hence, I originally thought that a “thin” table approach would be best. This was a disaster. The model worked but performed mind-numbingly slow. How could it be, when I used as few columns as possible?

I finally realized the problem here is that these costs are not facts. They are really dimensions. Sure they are numbers that we will calculate against, but they are still dimensions that either do not change or might slowly change (with the addition of date fields). I was so used to thinking that any column with lots of numbers we are calculating with should be a fact. Welcome to database design 101

I developed a small example application to illustrate both models and illustrate the performance differences. There are about 150,000 sales records, 10 cost centers and 80 products. So there are about 800 costs that needed to be housed either in one long column or 10 columns of 80. I ended up with this data model (so that I could create two tables each using one of the cost sets)

I then created a pivot table from the “thin” table using set analysis and a similar pivot table from the “fat” set using the same expressions without the set analysis and referring to the respective data columns.

I also created a Memory statistics file to highlight the difference in calculation time between the two objects:

You can see that the “Fat” set is almost 5 times as efficient in the example. And look at the memory advantage in Bytes!

So maybe next time I will take a more thoughtful look at the data before deciding on a model because what can appear as a fact can really be a dimension. And I should always be aware that regardless of the type of data, sometimes more columns really are better.

Conditional Dimensions and Expressions – Tips and Tricks

noreply@blogger.com (Anonymous) — Mon, 30 Jan 2012 14:49:00 +0000

With the shipment of QlikView 11 comes an incredibly cool feature: Conditional Enablement of Dimensions and Expressions. That is “fancy speak” for being able to change the columns in your chart based on any number of conditions.

This can have various purposes but the one I am using most often is the ability for your users to create their own Ad Hoc reports. Let’s create an example to illustrate some tips I have learned.

You can find the example qvw here.

The first thing you will want is an inline table of the dimensions and expressions you will want the user to be able to select. I would recommend two columns, one for the descriptor and one to uniquely identify it by a single letter.

AdHoc_Dimensions:
LOAD * INLINE [
    _Dimension, _Dim_ID
    Order Num, A
    Product, B
    Class, C
    Color, D
    Year, E
    Month, F
];

This table will not be associated to any other tables in your model. Depending on your need, you might create a table for your selectable dimensions and one for your selectable expressions.

Now that you have your selectable dimensions and/or expressions to choose from, you can go to the layout and create list boxes to show the options. I like to add the Clear and Select All menu icons to the captions for these lists. It might also help to add a help message or label the caption with “Pick the columns to display” or similar.

Now create the object that the user will be customizing. You can put conditional dimensions or expressions in any kind of object. Here, we will create a straight table. Add all the fields referenced by your dimension list. And add the calculated expressions that relate to any selectable expressions you created.

This is where the magic happens. In the dimension properties, check the option for “Enable Conditional” and enter the following code:

=SubStringCount(Concat(_Dim_ID, '|'), 'A')

You will want to add similar code to each dimension, changing the letter to correspond with what you used in your inline table. Then do the same thing for your expressions if desired.

The last thing to do is add a Calculation Condition to the general tab of the object so that the object only displays when at least one dimension and expression is selected:

GetSelectedCount(_Dimension)>0 AND GetSelectedCount(_Expression)>0

I then add a message to Error Messages for “Calculation condition unfulfilled” that reads similar to “Please choose at least one dimension and one expression to display... “

Click OK and now you should have a working Ad Hoc report. Choose various dimensions and expressions and the chart should change according to your selections.

Some tips and warnings:

I use a letter ID because there is less potential of accidently displaying more dimensions than intended with the SubStringCount function. For example, 1 will return a result for 1, 10, 11, 21, 100 etc. Using the actual dimension names can also be troublesome especially with columns like “Invoice” and “Invoice Line” for example.

As of this writing (QV11 Initial Release), there is a bug in interactive sort for columns. I haven’t been able to isolate the behavior.

Also, I have found that when using a pivot table, there is sometimes an issue when attempting to pivot a dimension horizontally.

Even with the pivot and sortation issue, I am finding that my clients are very happy with this new flexibility. It was something not easily accomplished in prior versions.

I hope you have success with this and are able to expand the uses further.

QlikView Designer Quick Tips

noreply@blogger.com (Anonymous) — Fri, 20 Jan 2012 01:53:00 +0000

Here are my favorite QlikView designer tips. These are just some quick tips and shortcuts. Most of them are common knowledge. Maybe there are a few that you might have forgotten about or maybe a few are genuinely new to you. Feel free to add your own tips. We could all use them!

Pixel by Pixel Movement – Everybody knows how difficult it is to get the alignment of objects exactly right. To move an object a pixel at a time, select it and then hold down ctrl while using your arrow keys.

10 Pixel Movement – Hold down ctrl + shift while using your arrow keys to move an object 10 pixels at a time. Using the ctrl and ctrl + shift techniques give you very flexible yet precise movement. Try using these after you have zoomed in 200%. Now you are cooking with grease!

Move Locked Objects – What a pain. You just finished locking down all your objects by unchecking Allow Move/Size and the boss wants you to change the layout. Just leave the items locked. Hold down the alt key and you can freely drag and size objects.

Expression Overview – Go to Settings --> Expression Overview to access all the calculations in your app. The great thing about this is if you have many expressions that need to be slightly changed in the same way, you can use Find/Replace to locate the part to be changed and replace all of them in one step. That beats searching every object in every tab to make your changes.

Move Chart Parts – Use Ctrl-Shift when selecting an object to engage the separate parts of a chart. This allows you to move and resize the legend, axes and chart titles with ease.

Document Properties – Settings --> Document Properties are very valuable, especially if you hid a sheet that you later need to access. Find all your sheets and access their properties on the Sheets tab. The General tab houses the styling options and the Triggers tab gives allows you to trigger actions based on many different events.

Double Click Columns – Just like in MS Excel, you can double click a column edge to have it automatically fit your heading and data. You can also right click and select Fit Columns to Data.

Cool Shortcuts:

Ctrl-T	Table Viewer
Ctrl-E	Script Editor
Ctrl-R	Reload
Ctrl-Alt-D	Document Settings
Ctrl-Alt-V	Variable Overview
Ctrl-O / Ctrl-C / Ctrl-X / Ctrl-V / Ctrl-Z / Ctrl-S	If you aren’t aware of open / copy / cut / paste / undo / save, you are in the wrong business.

QV Source and Google Analytics – Retrieving Data

noreply@blogger.com (Anonymous) — Tue, 10 Jan 2012 16:06:00 +0000

I have been fortunate enough to work with a new product from Industrial Code Box. QVSource is a great tool that allows you to connect QlikView with all sorts of web-based data services. There are some very interesting connectors including Facebook, Twitter, Klout, LinkedIn, OData, Infochimps and YouTube available with this tool.

But the connector that I have most used so far is the QVSource connector for Google Analytics. Google rules the web statistics space as it does anywhere else they enter. And included with Google Analytics is the ability to feed data directly through an API. QVSource makes use of this feed and converts it into QlikView script that any developer can use to harness your Web Analytics with the power of QlikView.

The first step for creating an application is to open up QVSource and configure the connector.

You will enter the GA user and credential. Then click the URL build link.

This will open up the GA Data Query Feed Explorer. You must be logged in to a gmail account that has read access to the GA account. If you click in the “click to list your accounts” box and nothing happens, make sure:

Your gmail account has the correct access.
Try Logout and Reauthorize.
Or use Google Chrome as your browser. The accounts box does not authorize correctly when using IE, even if you are signed into gmail.

Then you can use different combinations of dimensions and expressions to design the queries you need. Keep in mind that GA only allows a maximum of 7 dimensions and 10 expressions. Also, GA does not provide a unique user ID (against their policy). So it can be a bit of a challenge to get down to the lowest detail possible. As we know, QlikView breathes deeply in that granular level. I have found that creating multiple queries can be effective if most of the dimensions are repeated to form a key and then you can cycle through any other needed dimensions. In my case, I used latitude, longitude, date and hour as a key for all my queries. That allowed me to bring in other dimensions 3 at a time. Disregard the note that says max 10,000 records. The query window is limited by 10,000, but the API call is not.

Once you find a query you want to use, simply copy the URL that forms above the query box and paste it into the URL box in QVSource. Then select the Connector Data tab. Select Data and then click the QlikView Load Script tab. You will now see the load script you will need to use in your QlikView application.

Simply paste your script into QlikView and reload. One thing to remember is that QVSource will need to be open any time you reload the application. I found it helpful to open QVSource using Windows scheduler so that it works like a service. Alternatively, you could use QlikView server to open the program before the reload runs.

You now have Google Analytics as a source for QlikView. I am having lots of fun duplicating GA visualizations and surpassing them with my own creations. And the client I have developed the QlikView document for is ecstatic with the results. We are now talking about adding in Twitter and Facebook Page statistics to get a more integrated Web Strategy application.

Hierarchy – Reflexive Transitive Closure

noreply@blogger.com (Anonymous) — Wed, 04 Jan 2012 01:21:00 +0000

I have always wanted to make more use of the list box with the tree view option. It has several advantages that make it a great way to select dimensions in QlikView:

Ability to fit several selectable fields into a single object.
Naturally shows the hierarchal relationship for the displayed fields.
Intuitive to navigate for anybody who has come within 100 yards of a computer.

But many people, including myself have shied away from using the Tree View option because it is hard to get right. What developers quickly realize is that selecting a non-leaf parent does not associate to the children of that parent. This is because the QlikView function, Hierarchy, creates an expanded nodes table, but does not create the optimal Reflexive Transitive Closure style of this table.

Expanded Nodes table using Hierarchy function alone:

NodeID	Name1	Name2	Path	Depth
1	Agatha		Agatha	1
2	Agatha	Aaron	Agatha/Aaron	2
3	Agatha	Adam	Agatha/Adam	2

In above table if Agatha has no sales because she is the manager, when someone selects her in the Tree View list box the sales for children Aaron and Adam will not appear. In addition, this behavior is even more confusing because the path changes depending on whether Agatha is expanded or not. This results in the association sometimes working and sometimes not working.

Reflexive Transitive Closure:

NodeID	Name1	Name2	Path	Depth
2	Agatha	Aaron	Agatha	1
2	Agatha	Aaron	Agatha/Aaron	2
3	Agatha	Adam	Agatha	1
3	Agatha	Adam	Agatha/Adam	2

In this version, the association will work correctly every time. This is because the NodeID (linking back to your transactions) has extra rows to account for the changing path as one clicks higher in the hierarchy. Only the leaf nodes (NodeIDs existing in transactions) need to be included in the table.

Now the question is how would you create this table. I have created a downloadable example with the actual script but here are the steps.

Load in transactions first because they will be used to identify the leaf nodes.
Use an exists(NodeID) or a join to add an IsLeaf flag to your adjacent nodes data. You can load the adjacent nodes table using exists in one step. I chose to use a join after the adjacent nodes load to keep the solution portable.
Use the Hierarchy function to flatten your adjacent nodes table into an expanded nodes table. You will want to use the Path option and remember to leave in your IsLeaf field. This leaves you with the traditional hierarchy table that includes all nodes.
We only need the leaf nodes so now we resident load this table into a new table, only loading the leaf nodes and sorting by the depth of the levels.
Now we create a looping structure going from the deepest level and working up to the highest parents. Each loop adds records up the hierarchy so that a child has a corresponding path all the way up. The loop will run for as many levels as you have minus 1.
Then just drop any of the temporary tables you created and any extraneous fields not needed in the final data structure.

So this will create an expanded nodes table with reflexive transitive closure. There are still some issues with the way QlikView handles this:

You will want to select Suppress Horizontal Scroll Bar in the list box due to a rendering issue in AJAX.
There will still be some confusion in selecting because depending on whether the parent is expanded or not, the children might not look selected. I have not been able to work through that and have not had successful results trying to tie a trigger to it.
The last item is that if you have a model where the parents have transactions as well as the children (different levels), you cannot isolate the parent’s transactions in the list box, since we have forced the association to the children. The solution here would be to either go back to the traditional hierarchy table or to use an additional list box to select a single parent. I mostly find this to not be an issue since usually the transactions are only at the lowest level.

Please enjoy and I look forward to any comments or additional analysis on this topic.