hypanis.ru Qlikview Set Analysis {1} Identifier - BuffaloBI.com

Qlikview Set Analysis {1} Identifier

Sometimes when building expressions in Qlikview, you need to setup a formula to read all possible records, regardless of the current selection(s) made. This is where Qlikview Set Analysis comes into play! There is a simple set identifier {1} you can insert into your expression that will do the trick. This post demonstrates a simple example that will illustrate it’s use.

Qlikview Set Analysis – Full Record Set Regardless of Selections

From Qlikview Help:

There are two general syntax rules for a set expression:

  • A set expression must be used in an aggregation function.
  • A set expression must be enclosed by braces, {}.

The {1} Identifier: Represents the full set of all the records in the application, irrespective of any selections made.

Example of using the {1} Identifier:

First, load the following sample data using a LOAD INLINE script:

Next, setup three list boxes and a straight table chart, like the following:

Notice the three expressions used in the straight table chart:

  • Expression 1: =Sum(Actual)
  • Expression 2: =Sum({1}Actual)
  • Expression 3: =Sum(Actual)/Sum({1}Actual)

Expression 1 is a simple Sum which which dynamically updates based on list box filter selections.

Expression 2 is where the set analysis comes into play, using the {1} identifier. When any selections are made in the list boxes, this formula will still calculate the Sum based on the full set of records in the application (ignoring filters).

Expression 3 uses a combination of expressions 1 & 2, to calculate a percent of total for each selected filter.

So let’s say you select the Year 2019:

Notice the following behavior:

  • Expression 1: =Sum(Actual) This will sum the respective “Actual” totals for each team, based on the filter of “2019”. You can map the totals based on colors Green and Orange above.
  • Expression 2: =Sum({1}Actual) This formula sums the entire total for each team, regardless of the filter applied. You can map the totals based on colors Red and Blue above.
    • I added a custom expression list box to display all the data in the application. The set analysis formula totals all the Actual values for each team, regardless of the filter applied. This is the power of Set Analysis!
  • Expression 3: =Sum(Actual)/Sum({1}Actual) This formula then divides Expression 1 by Expression 2, to display a percentage total.

You can play around with selecting various filters to further examine how the Set Analysis {1} identifier works.

Enjoy!

1 thought on “Qlikview Set Analysis {1} Identifier”

  1. Thanks for this topic! Really helpful.
    Would it be possible for you to come up with a post for Else If / For Next functions. Please if you have the time, would love to have an example from you. Somehow the way you explain these things makes it seem very simple!

    Keep posting 🙂
    Cheers!

    Reply

Leave a Comment