topic Re: Correlation with timeshift in App Development

Correlation with timeshift

pdimitrakis — Mon, 21 Oct 2024 10:14:19 GMT

Hello everyone,

I want to calculate the correlation of two variables but I know that there exists a timeshift/delay between them.
An example data is the following:

DAY	VAR1	VAR2
01/01/2002	1	2
02/01/2002	2	3
03/01/2002	3	4
04/01/2002	4	5
05/01/2002	5	4
06/01/2002	4	3
07/01/2002	3	2
08/01/2002	2	1
09/01/2002	1	2
10/01/2002	2	3
11/01/2002	3	4
12/01/2002	4	5
13/01/2002	5	4
14/01/2002	4	3
15/01/2002	3	2
16/01/2002	2	1
17/01/2002	1	2
18/01/2002	2	3
19/01/2002	3	4
20/01/2002	4	5
21/01/2002	5	4
22/01/2002	4	3
23/01/2002	3	2
24/01/2002	2	1
25/01/2002	1	2

If I use the traditional correlation, the outcome is 0.67 but i know that if i shift the first var by one day, the correlation will be 1.

Is there a way to calculate the "real" correlation (meaning 1) without knowing the time shift?

Thanks in advance,
Panagiotis

Re: Correlation with timeshift

marksouzacosta — Mon, 21 Oct 2024 13:59:58 GMT

That sounds like a topic for you @igoralcantara

Regards,

Mark Costa

Re: Correlation with timeshift

igoralcantara — Mon, 21 Oct 2024 14:11:54 GMT

In that case I would use a technique called Cross-Correlation. You can also find the Optimal Shift, the shift that produces the highest correlation.

This is easily done in Python. In Qlik Script is not so simple, but in the UI, there are some ways. From the top of my head, remembering when I did this years ago, you can try something like:

Use Above(VAR1, 1) to shift VAR1 by one day backward.
Calculate the correlation between this shifted VAR1 and VAR2.
Repeat for different shifts until you find the maximum correlation.

This explains what a cross-correlation is:

What Is Cross-Correlation? Definition, How It's Used, and Example

Re: Correlation with timeshift

pdimitrakis — Wed, 23 Oct 2024 06:34:58 GMT

Thanks a lot!

The Above() function works great in both the visualisation for visual overview and the correlation evaluation.

If the time shift is known it is the optimal solution.

As I understand If the time shift is unknown, there is not automatic solution inside Qlik.

We have to either test possible values and see the best result or do calculations before loading to Qlik, either by testing the values or calculating the Cross-Correlation, and feed the ready result to Qlik.

Am I right or is there another workaround?

What bothers me is that the two timeseries may be irrelevant in general but follow the above pattern only for a month. If we select that month as a timespan, the pre-calculated time-shift or Cross-Correlation will not be able to capture the temporal behaviour.

Re: Correlation with timeshift

igoralcantara — Wed, 23 Oct 2024 13:07:35 GMT

Maybe what you need is a Time Series instead.

What exactly are you trying to achieve? What business question are you answering?

Re: Correlation with timeshift

pdimitrakis — Wed, 23 Oct 2024 17:24:20 GMT

I am looking into data including social media posts information and show viewership.

I want to check if a certain "kind" of posts drives the changes to viewership.

In a simplified example, whenever I post with positive reviews, the show viewership goes up and when I post with negative reviews, the viewership goes down.

But the effect of my reviews is not instant but comes with an unknown delay.

I want to check if there is a connection between various kinds of posts and show viewership, product sales etc.
The effect may be temporal (i.e. a certain campaign) and that is why I would prefer the analysis to be dynamic and not static (in python before loading to Qlik).

Re: Correlation with timeshift

igoralcantara — Wed, 23 Oct 2024 20:02:14 GMT

Oh, I see. This sounds like an A/B Testing or maybe a Multi-Armed Bandits if it is an ongoing campaign. Check Data Voyagers, I wrote a couple articles about it, using Qlik.

Re: Correlation with timeshift

Qrishna — Wed, 23 Oct 2024 20:42:30 GMT

Hi im no champ in stats, but i would say if you see you have a linear relation VAR2 = k × VAR1 + b kind of y=mx+c we use it for linear geometry, if you could figure out constants k and b then you could do something like below:

Data:
LOAD DAY,
VAR1,
VAR2
FROM
[https://community.qlik.com/t5/App-Development/Correlation-with-timeshift/td-p/2488146]
(html, codepage is 1252, embedded labels, table is @1);

// Define constants for k and b
LET k = 2; // Example multiplier
LET b = 1; // Example bias

// Creating Var3 based on the formula
Qualify *;
d:
LOAD
DAY,
VAR1,
VAR2,
$(k) * VAR1 + $(b) AS VAR3 // This calculates Var3 as k * VAR1 + b
RESIDENT Data;

when you dont know the constansts(linear relation) You could reverse engineer to find of constants:

Finding out constanst from the given data, basic technique

From the equation VAR3=k×VAR1+b

we can create the following equations from the data:

1. For the first row:

3=k×1+b (1)

2. For the second row:

5=k×2+b (2)

3. For the third row:

7=k×3+b (3)

Solving the Equations

You can solve these equations to find the values of k and b.

1. From Equation (1):

b=3−k (4)

2. Substituting (4) into Equation (2):

5=2k+(3−k)

Simplifying:

5=2k+3−k

3. Using (4) to find b:

Substitute k back into Equation (4):

b=3−2=1

Result

Thus, the constants are:

• k=2

• b=1

im sure it would be much more complex in real scenarios, but this gives u a basic understanding.

If you want to explore cross-correlation at various lags,
you might need a more complex setup (like creating lagged versions of your variables).
for simplicity:

Lagged Variables (if necessary):
If you were to create lagged versions of your variables (e.g., VAR1 shifted by one time period), you could do something like this in the script:

DataWithLag:
LOAD
DAY,
VAR1,
VAR3,
Previous(d.VAR1) AS d.Lagged_VAR1
RESIDENT d;

Re: Correlation with timeshift

pdimitrakis — Thu, 24 Oct 2024 07:13:31 GMT

Very nice article and explanation of Multi-Armed Bandits.

I see great value in it after deciding the evaluation metric of each post or type of post (analogue to ad in your example).

Re: Correlation with timeshift

pdimitrakis — Thu, 24 Oct 2024 07:30:26 GMT

Great and very analytic answer.

Solving the system of equations to find the time and scale shift is plausible outside Qlik and if that is the case, processing the data with a python library to calculate the actual Cross-Correlation coefficient like in signal theory would cover the real scenario.

Your idea of creating lagged Variables a priori is a way to go if the python processing is out of the question. Like a simplified Cross-Correlation, creating 10 (or more) different lagged versions of VAR1:

VAR1_1 = Above(VAR1, 1)

VAR1_2 = Above(VAR1, 2)

etc

and selecting as the Cross-Correlation approximation the higher correlation of VAR1_i to VAR2

Re: Correlation with timeshift

Qrishna — Fri, 25 Oct 2024 01:57:37 GMT

Yeah i would prefer python over qlik if you dont have any UI calcs. Also in qlik you dont need to create

VAR1_1 = Above(VAR1, 1)

VAR1_2 = Above(VAR1, 2)

you write a little for loop with n, nbeing the number lagged variables and it creats as many lagged fields as you want.

Re: Correlation with timeshift

pdimitrakis — Fri, 25 Oct 2024 07:41:31 GMT

The only drawback of python processing is that we are consuming the Qlik analytics through an html mashup and the users accessing it will not be from our team.

So the actual Cross Correlation is something we can test in python but the final users will only have access to the approximation using specific time shifts.

Multiple time shift variables using a loop with a high number of n will improve the approximation error. And also we might try to select smaller time intervals for the shifts, i.e. hours instead of days.