topic Re: Data Islands and Set Analysis in QlikView

Data Islands and Set Analysis

Fri, 01 Nov 2013 20:26:32 GMT

Hi,

I have two data islands in my data model and I've been using IF statements to sum values over these data islands. For instance:

=Sum(if([Island Date] = [Calendar Date] AND [Island Country] = [IP Country ISO], Signups))

This returns the sum of signups where Calendar Date and IP Country ISO equal their respective data islands. Issue is it's very resource intensive and not scalable with our data.

I would like to come up with a solution using set analysis. So far I've tried two methods:

1) Indirect set analysis using an expression like this:

=Sum({$<[Calendar Date]=P({$} [Island Date]), [IP Country ISO]=P({$} [Island Country])>} Signups)

2) The Only() function:

=Sum({<[Visit IP Country ISO]={"$(=only([IP Country ISO]))"}>} if([Visit Date] = [Island Date] , Visits))

The issue with these is that they only work when a selection is made and in my app this will not be the case as I need to associate sales and traffic values to countries in the absence of any selections.

If anyone has any tips on how to do this via set analysis, they would be much appreciated. Basically I'm looking to translate the IF statement above into set analysis.

I've attached an example to better illustrate what I'm talking about.

Best,

Matt

Re: Data Islands and Set Analysis

Fri, 01 Nov 2013 23:56:42 GMT

take a look at this text, I think alternative state is more interesting than data island

Page 122 at qlikview reference manual

14.14 Alternate States

Description

QlikView allows users to detach a chart from the underlying data model. When a chart is detached, the

object no longer responds to user selections. When the user re-attaches the object, it instantly updates to

reflect the current state of the selections.

Alternate States are similar to this. The QlikView developer can create multiple states within a QlikView

document and apply these states to specific objects within the document. The end-user can create copies of

these objects (server objects) and then put those objects into different states.

The most important difference between Detach and Alternate States is that ALL objects in a given state will

respond to user selections made in that state. Objects in a given state are not affected by user selections in

the other states.

Alternate States are not accessible in the load script. They are a UI feature.

The developer invokes the Alternate States dialog from Alternate States... button on the Document Properties:

General tab. The QlikView developer can create any number of states within the QlikView document

and provides a name for each state created. Once Alternate States functionality is enabled, the

QlikView developer can also create new states from within screen objects. End-users who access QlikView

documents from a QlikView Server can make use of Alternate States but cannot create Alternate States.

Note!

Alternates States functionality is enabled by a QlikView developer and should be used with caution as it

can cause great confusion with end-users.

Re: Data Islands and Set Analysis

Gysbert_Wassenaar — Sat, 02 Nov 2013 09:00:57 GMT

You can't use set analysis. The sets are calculated per chart, not per row. So, it's either use the if statements or separate charts.

Re: Data Islands and Set Analysis

Sat, 02 Nov 2013 15:14:27 GMT

Thanks for the help, I'll have to stick with the if statements.

Question: can you explain this variable to me: =concat(distinct chr(39) & [Island Date] & chr(39), ',')

Thanks again for the help.

Matt

Re: Data Islands and Set Analysis

Gysbert_Wassenaar — Sun, 03 Nov 2013 11:58:23 GMT

Put that expression in a text box and make some selections in Island Date. That way you can see what it does.

Re: Data Islands and Set Analysis

Mon, 04 Nov 2013 15:34:45 GMT

thanks!