Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How can i calculate a "moving value"

Hello everybody !

In a project on which i'm working, i would like to calculate something which is based on two values. For instance, if i want to display this value at the time "N", i will need to use another field (let's call him CF) at "N" and i need another one, linked to "N-1" time.

For instance,

Time Sales Calls
N-1 400.000 90
N 1000.000 20

And i want, for N, to get the value :
20/400.000

Is there a way to find out it, without Set Analysis ? 😉

Thanks a lot !!

PS: QlikView is AWESOME ^^

8 Replies
johnw
Champion III
Champion III

How about Calls/above(Sales)? And if you want something specific for the first row, something like if(rowno()=1,<some expression>,Calls/above(Sales)).

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

Search the Help Section for "Inter Record Functions", particularly - function above(). It should help you solve this problem.

Oleg

Not applicable
Author

If i have several values between the different times, for instance,

Time Region Sales #Calls

N-1 Region Sales_R1_n-1 Calls_R1_n-1
N-1 Region2 Sales_R2_n-1 Calls_R2_n-1
N-1 Region3 Sales_R3_n-1 Calls_R3_n-1

N Region Sales_R1_n Calls_R1_n
N Region2 Sales_R2_n Calls_R2_n
N Region3 Sales_R3_n Calls_R3_n

I assume i'll use Calls/above(Sales,Count(DISTINCT Region))
to calculate the right value per region ?

Thanks 😉

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

No, with your structure, you can't use above() - you'll get the wrong Region's data. If you could swap between Time and Region, then you could do it.

Oleg

Not applicable
Author

I would have thought QV provided a way to get the values in this order... It's weird...

So there 's no way to handle Orpheus issue (other than swap Time & Region?).
I think he needs to keep this order to view in a chart as a first dimension the Time and as a seond the Region...

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

Perhaps it can be done with Set Analysis, but the logic of it would be very tangled...

johnw
Champion III
Champion III

Ah, but we were asked to do it without set analysis. One approach would be to handle it during the load.

Main:
LOAD * INLINE [
Time, Region, Sales, Calls
1,A,500,10
1,B,300,15
1,C,200,20
2,A,600,20
2,B,600,15
2,C,400,10
]
;
Chart:
LOAD
Time + 1 as "Time"
,Region
,sum(Calls) as "Previous Total Calls"
RESIDENT Main
GROUP BY Time, Region
;

And then do sum(Sales)/"Previous Total Calls" in the chart. I'm not sure how well that would work with real world data, though.

johnw
Champion III
Champion III

The sum(Sales)/above(sum(Calls)) will also work in a pivot table, so there's another option if that's doable. For instance, have Region across the top, Time and your expressions down the side. Attached are examples of that and the previous solution.