Set analysis that ignores all selections except a few

How do you get Set Analysis to ignore all selections except for a few fields?

We have a calendar table, linked to a fact table and some selections are made.

We now want to work out a calculation based on the date range selected (eg WTE) - but there is a problem. As the data is linked, our non-calendar selections (in this case employee) are only linked to some of the dates - infact for 2014 only in the month of May!

So any calculations based on number of days, etc will only return what is in the data. Furthermore, using the {1} set analysis to ignore all selections will ignore also the selection in year, but we need that. And what if you have hundreds of fields to list?

Moving average chart.... QlikCommunity discussion

Following on from my post on six sigma charts here, a discussion has opened up on QlikCommunity on moving the average once the n-points rule has been violated.

http://community.qlikview.com/thread/103433

The problem is that the calculation for the average is iterative- once you work out a new average, the test for the points moves. This makes it tricky to achieve in the application, although it can be done in the script.

Any ideas anyone?


Erica

Confidence intervals and funnel plots: please read this first! #1

At work, people are using more and more confidence intervals or funnel plots in their data as part of their decision making processes. Which is great!

However I think that putting them on a chart can lull people into a false sense of security. Those little lines can legitimise the labelling of points of data as outliers. People take action and make decisions based on it, so they can be dangerous. It's important to get it right, and often assumptions are made and analysis copied without much thought.

Here are the 3 of the most common mistakes I've seen:

How to reload on top of your existing data in your dashboard (bit of a cheat)

Did you know you can use a simple statement to load data back in from your own application? This is handy if you need to tweak data and avoid having to do a large reload.

How to create 4,294,967,296 rows of data from 1 duplicate in 5 easy moves

Like this:

You load data from a source with an allegedly unique key.

Then you need to do some calculations on it, using data from other sources say, or perhaps do some calculations in the script (to make charts faster in the app) so you join the table back onto itself.

Imagine there's a duplicate, 2 lines with the same key in. When you join it onto itself, each line will replicate X the number of rows it matches in the table that you join it with. IE 2 rows will turn into 2 X 2 = 4 rows:

Data			Data_2			Data - after join
Key#	Data		Key#	Data		Key#	Data
1	a		1	a		1	a
2	b		2	b		2	b
3	c		3	c		3	c
4	d		4	d		4	d
4	e		4	e		4	e
						4	d
						4	e

If you were to then join the table onto itself again, each row with key #4 will join back onto itself 4 times, creating 4 X 4rows = 16.

So far so obvious.

Qlikview Lean tips #1: How to highlight series of points that violate control rules on a chart

Highlighting patterns of points on a chart






Control charts are charts that plot some sort of quality measurement, for example number of defective goods in a batch or the weight of packets of food. The measurements are then plotted over time to see if there are any patterns. I won't go into too much more detail, because this is not the place for it (and I don't want to bore anyone that already knows it!).

You then draw "control limits" - lines above and below the average and then investigate any points that fall outside those lines, or meet some criteria. What those criteria are, and where to set the control lines are dependant on the quality control system you have in place, and the type of data etc. Lean Six-Sigma is perhaps the most famous set of rules for determining when to take action.

In general the rules are often things like:

• X number of points above the average line
• X number of points increasing each time.

It's fairly easy to create a control chart with the limits, but a bit more complicated (judging from questions I get asked) to highlight every single point in a "run" that has violated the pattern, especially dynamically (as in not in the script). It's easier to just mark 1 (the start or end).
 
So how do you highlight "X points in a row"?

Hierarchical sheets navigation in QlikView ("breadcrumbs")

Ever wondered how you can get website style navigation in QlikView?

By that I mean you start with a home page, and a menu, and as you whittle down the categories to get what you want they stick on the top of a screen with an arrow to show that you've been there.  (If anyone can think of a better description then let me know...!)

edit: I've just been reminded it's also called breadcrumbs, thanks speros!

Examples:


Home>Our company>Careers>Vacancies>Vacancy search results

 

Someone asked me for this today. The Dashboard that I am doing for them will potentially have lots of tabs, and they might want to add and change it regularly! So I spent a while trying to get a solution that was as systematic as possible.

This is also a handy solution for applications that are going to be used on phones or tablets, as space is even more at a premium and I'd prefer this to having to scroll horizontally. (In most cases).

Oh, and I'm not allowed to use macros...