topic Re: calculation based on range in QlikView

calculation based on range

vijetas42 — Fri, 16 Feb 2018 12:45:39 GMT

Hi,

Can we calculate value based on range,

suppose in first table i have

id rate R_value

101 0.2 1000

101 0.3 2000

and second table i have

Id value

101 1500

then, for value for 1500 should be counted based on for first 1000 it should consider range 0 to 1000 for which rate is 0.2

and remaining 500 it should consider 0- 700 range for which rate is 0.3

Re: calculation based on range

Anil_Babu_Samineni — Fri, 16 Feb 2018 14:55:06 GMT

May be look https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/MappingFunctions/ApplyMap.htm

Re: calculation based on range

petter — Sat, 17 Feb 2018 03:23:34 GMT

It is possible - have a look here:

The expression for the first table (last column) V';

If(

If( Above(TOTAL Sum(ID)) <> ID ,

If( Sum(V) < Sum(VL)

, Sum(V)

, Sum(VL)

)

, If( Sum(V) < Sum(VL) , Sum(V) , Sum(VL)) - Above(TOTAL Sum(VL))

) > 0

If( Above(TOTAL Sum(ID)) <> ID ,

If( Sum(V) < Sum(VL)

, Sum(V)

, Sum(VL)

)

, If( Sum(V) < Sum(VL) , Sum(V) , Sum(VL)) - Above(TOTAL Sum(VL))

)*R

)

The expression for the second table has to use the advanced aggregation function Aggr since dimensions are missing from the table:

Sum(Aggr(

If(

If( Above(TOTAL Sum(ID)) <> ID ,

If( Sum(V) < Sum(VL)

, Sum(V)

, Sum(VL)

)

, If( Sum(V) < Sum(VL) , Sum(V) , Sum(VL)) - Above(TOTAL Sum(VL))

) > 0

If( Above(TOTAL Sum(ID)) <> ID ,

If( Sum(V) < Sum(VL)

, Sum(V)

, Sum(VL)

)

, If( Sum(V) < Sum(VL) , Sum(V) , Sum(VL)) - Above(TOTAL Sum(VL))

)*R

)

, ID , R )

)

Here is the sample data I used:

RANGES:
LOAD * INLINE [
ID, R, VL
101, 0.2, 1000
101, 0.3, 2000
101, 0.4, 4000
102, 0.25, 750
102, 0.27, 1000
102, 0.28, 1200
102, 0.29, 1500
];


VALUES:
LOAD * INLINE [
ID, V
101, 1500
102, 1300
];

Re: calculation based on range

vijetas42 — Tue, 20 Feb 2018 08:44:41 GMT

Is there anyway to handle this scenario at script level.