Data Islands and Set Analysis

[#1]

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

[#2]

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.

[Gysbert_Wassenaar · Accepted solution]

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.

[#4]

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

[Gysbert_Wassenaar]

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

[#6]

thanks!