Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ^^
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)).
Search the Help Section for "Inter Record Functions", particularly - function above(). It should help you solve this problem.
Oleg
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 😉
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
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...
Perhaps it can be done with Set Analysis, but the logic of it would be very tangled...
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.
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.