Complicated user request: How to deal with expressions that change over time in QlikView?

[mwallman]

Hi all,

I have a rather complicated user request from a user.

I have some data where there are over 300 metrics. These metrics will change over time. So for example you might calculate sales measure differently from Jan 2009 to Dec 2009 compared to Jan 2010 to Dec 2010.

The table might look like:

MetricName	Expression	From	To	Jan2015	Feb2015	Mar2015	Jan2016	Feb2016	Mar2016
Sales	Sum({<Rate={34}>}LineSalesAmount)	Jan2016	Feb2016				0.2	0.2	0.4
Sales	Sum({<Rate={34, 36}>}LineSalesAmount)	Mar216	Jul2016				0.4	0.7	0.1
Orders	Sum(OrderRecordCounter)	Jan2015	Aug2015	1.5	1.3	1.4
Orders	Sum({<Multiplier={2}>}OrderRecordCounter)	Sept2015	Dec2015	1.8	0.4	1.1

In the above, you can see the expression changes over time as well as some sort of rate associated is used differently.

How can I user use a certain expression for data at a certain time period?

I imagine this is a vague question which I am fully not sure how to explore so any help would be great!

[jonathandienst]

This will require a combination of:

• Add a sequence number to the instances of a particular metric (eg Sales.Sequence, Orders.Sequence)
• Interval matching to relate the metric sequence number with a month range to the individual months (and storing the expression sequence number for the month)
• Construction of a Pick() compound expression by concatenating the expressions and sequence numbers into a variable to get something like:

     vExp.Sales = Pick(Sales.SequenceNo, <... expression 1 for sales ...>, <... expression 2 for sales ...>, ....);

     vExp.Orders= Pick(Order.SequenceNo, <... expression 1 for orders ...>, <... expression 2 for orders ...>, ....);

    (you would build these expressions in your script)

• Use these expressions in a table that is dimensioned to ensure a single possible value for each of the metric sequence numers (eg include Month as a dimension).

Pick() expressions like these can be performance killers, so you may have performance issues if your data set is large.

Hope this helps you to get started...