Alternate states – easy data comparisons

It’s been a while since I posted something here, but today I am coming back with a short article about alternate states and using them for creating data comparison sheet. Let’s jump into it!

You can find final qvw file at the end of this article 

First, we will need to create Alternate States in our QlikView document. It can be done by going to Settings, then General tab in  Document properties (or simply using CTRL + ALT + D shortcut), clicking Alternate States button and finally pressing “Add” button:

QlikPad 04-26-15 at 09.18 AM

Let’s add two states, I called them State 1 & State 2.

We must now add two sets of filters which will allow data comparison. I used CTRL + Q + Q shortcut in Editor to generate sample data (I modified it slightly to get 3 dimensions and 1 Expression).

Load Chr(RecNo()+Ord('A')-1) as Alpha, RecNo() as Num autogenerate 26;

if(RecNo()>=65 and RecNo()<=90,RecNo()-64) as Num,
Chr(RecNo()) as AsciiAlpha,
RecNo() as AsciiNum
autogenerate 255
Where (RecNo()>=32 and RecNo()<=126) or RecNo()>=160 ;

mod(TransID,26)+1 as Num,
Pick(Ceil(3*Rand1),'A','B','C','D') as [Dimension 1],
Pick(Ceil(6*Rand1),'a','b','c','d','e','f') as [Dimension 2],
Pick(Ceil(3*Rand()),'X','Y','Z') as [Dimension 3],
Round(1000*Rand()*Rand()*Rand1) as Expression;
Rand() as Rand1,
IterNo() as TransLineID,
RecNo() as TransID
Autogenerate 1000
While Rand()<=0.5 or IterNo()=1;

I have added two identical sets of list boxes and one text object, to show Average value of Expression. As you can see there it is not possible to make difference selections in, for example green Dimension 1 and orange Dimension 1. It’s because all objects currently share same State:

QlikPad 04-26-15 at 10.22 AM

We must now assign state to objects accordingly. State 1 – to green list boxes, State 2 – to orange list boxes.  To do that simply go to object properties ( ALT + ENTER) and choose State name from “Alternate State” drop down:

QlikPad 04-26-15 at 10.02 AM

When everything is done properly  you will see, that Green and Orange objects now operate independently:

QlikPad 04-26-15 at 10.21 AM

The great thing is that you can use both states in one expression and work with them as with two totally different data sets. To demonstrate that, let’s add one more text object, which will show absolute difference between Green and Orange averages. As Text enter:

=Num(Fabs(Avg({[State 1]} Expression) - Avg({[State 2]} Expression)),'# ##0,00')

It will look like this:

QlikPad 04-26-15 at 10.38 AM


That’s it! I hope you enjoyed this little tutorial. If you have any questions feel free to email me or leave a comment below.

Jakub Szurogajło

Self-motivated, certified QlikView developer and designer, currently working as Business Intelligence workgroup manager at Harman - Connected Services division.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.