calculation based on range

[vijetas42]

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

[Anil_Babu_Samineni]

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

[petter]

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

  ,0

)

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

  ,0

)

, 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
];

[vijetas42]

Is there anyway to handle this scenario at script level.