Correl function on Rolling Date

JohnDavids — Sat, 16 Nov 2024 19:01:30 GMT

Hi everybody,

I know how to use the Correl-function and I know how to use "rolling date". But I can't figure out how to combine them.

There is a simplified version of my problem.
The (simplified) Data looks like this:

DATE	REVENUE	(REVENUE (AltState))
01.01.2020	1.000	(800)
02.01.2020	1.000	(1.000)
03.01.2020	1.250	(900)
04.01.2020	1.500	(1.500)
05.01.2020	1.000	(1.600)
06.01.2020	850	(1.800)
07.01.2020	2.000	(850)
08.01.2020	1.250	(600)
...	...

[The REVENUE is actually only one data-set/column. The difference comes from another filter in the AltState. e.g.: In the "normal" state and the AltState the user can select two different countries, resulting in two different Revenue sums.]

For Example: In a table with"Date" as Dimension, I use the Correl function like this:

Correl( aggr( sum({$<Date= P(Date)>} REVENUE ) ,Date) , aggr( sum({AltState<Date= P(Date)> REVENUE ) ,Date) )

And the "rolling date" over three day like this:

rangesum( above( sum({$<Date= P(Date) >} REVENUE ) ,0,3 ) )

Now I tired to combine these two function/measures. I want to show the correlation between the revenue of both states summed up over three days.

I tried to use simply paste the second (rolling) measure inside the first. I tried the RangeCorrel function. I tried to combine Correl with RangeSum or RangeCorrel with Sum, nothing worked.

How can I use a combination of a rolling date and the correl function?

I added a demo excel file that show in column D how this could be done in excel. Maybe that helps you understanding my goal.

Any suggestion, tip or help is highly appreciated.

Kind Regards, John

Re: Correl function on Rolling Date

JohnDavids — Tue, 03 Mar 2020 07:24:33 GMT

Can some please help me here?
I still can't figure out how to achieve it.

Re: Correl function on Rolling Date

JohnDavids — Thu, 26 Mar 2020 06:36:50 GMT

I am still struggling with this one.

It seems to be so easy, but I cant work it out.

Re: Correl function on Rolling Date

JohnDavids — Wed, 29 Apr 2020 09:26:20 GMT

It is even possible to use Correl-function and a "rolling date" like I did in the Excel document in my first post? I still cant figure out how to do this.

topic Re: Correl function on Rolling Date in App Development

Correl function on Rolling Date

Re: Correl function on Rolling Date

Re: Correl function on Rolling Date

Re: Correl function on Rolling Date