4- Basic Formula

Basic Formula


Set Analysis

Set analysis is quite frankly one of the hardest things to grasp in qlikview! (For it’s syntax if nothing else!)
The best way to think of it is as a simple way to hard code search criteria into a formula, or hard code flexible criteria into a formula based on variables (but more of that later!)
In this case we will want to alter our load script for the sales data, and point it to a new excel file (download here)

Then reload the dashboard

To keep our examples tidy, we will create a new sheet to work on by hitting the new sheet button. If you can’t see this, make sure the design tool bar is enabled under view >toolbars.

Now, to keep some sort of uniform theme to the document, we will copy the selections boxes we have from our main tab. To do this:
Go back to the main tab, hold control, select a caption and drag it onto sheet1, you should see the cursor change to an arrow and + symbol.

Let go and the item will have duplicated itself on the Sheet1 tab in the same place.
For the months box, which doesn’t have a caption simply click on it, then choose edit > copy, then on sheet1 right click and paste sheet object. This should give you something like this

Now copy the chart over, and convert it to a table.
Make a new list box for ‘sales_year’ and edit it in the same way we did the months (Or copy the months one and edit it)
Hopefully your screen now looks something like this:

If it doesn’t then as always The dashboard to this point can be downloaded here

Play with the month and year selections and you can see that the figures and table adjust accordingly.
Think about a budget report for example, on that you will have monthly figures but also year to date figures. Surely this isn’t possible I hear you cry!
Of course it is, and this is where set analysis comes into play.

Set Analysis: Ignore ALL selections

Ok so the easy one first, how to set up a formula ignoring ANY and ALL selections made.
Add a new expression to the table (delete the sales quantity one if you still have it), using the following formula: =sum({1}sales_amount)
Call it something obvious like, ‘Total Data’. Note now when you make any selections, this figure never changes.

Leave a Reply