Databases Reference
In-Depth Information
• YTD flights for the previous Year
Sum({$<PeriodID = {"<=$(=Max(PeriodID) - 12)"},
Year = {$(=Max(Year) - 1)},
Quarter = ,
Month = ,
Period = >} [# Departures Performed])
• Flights for rolling-12 months:
Sum({$<PeriodID = {">=$(=Max(PeriodID)-11)<=$(=Max(PeriodID))"},
Year = ,
Quarter = ,
Month = ,
Period = >} [# Departures Performed])
There you have it, a complete set of formulas to help you create Point In Time
Analysis in your QlikView documents in a very simple fashion. We invite you to try
them out with the Airline Operations document and build some context around the
already created dashboard.
Storing set expressions into variables
Period-over-period comparisons are widely used in QlikView documents and, as
you've seen, they can get somewhat messy. Instead of writing the set expression each
time for every expression in which it is used, it's a good practice to store its definition
in a variable, which can then be called from anywhere in the QlikView document
where it's required.
Take, for example, the following expression. It calculates the number of flights Year-
to-Date, based on user's selections:
Sum({$<PeriodID = {"<=$(=Max(PeriodID))"},
Year = {$(=Max(Year))},
Quarter = ,
Month = ,
Period = >} [# Departures Performed])
From the preceding calculation, we can take the set modifier part (the part which is
enclosed in angle brackets) and define it as a new variable, called
vSetYTD
. Then,
we would use this new variable into a modified version of the expression presented
above as follows:
Sum({$<$(vSetYTD)>} [# Departures Performed])
