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
