Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
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.
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
Put that expression in a text box and make some selections in Island Date. That way you can see what it does.
thanks!