Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
New Contributor II

Correl function on Rolling Date

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:

DATEREVENUE(REVENUE (AltState))
01.01.20201.000(800)
02.01.20201.000(1.000)
03.01.20201.250(900)
04.01.20201.500(1.500)
05.01.20201.000(1.600)
06.01.2020850(1.800)
07.01.20202.000(850)
08.01.20201.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

2 Replies
Highlighted
New Contributor II

Re: Correl function on Rolling Date

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

Highlighted
New Contributor II

Re: Correl function on Rolling Date

I am still struggling with this one.

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