Skip to Content

HANA db 1.x - How to apply mutiple rates over years on a specific measure

I work with Hana 1.x

I have a calculation view with assets, attributes and measures over years (dataset 1) :

and another with different rates over years (dataset 2) :

Requirements :

Here is wanted output :

- Measure 2 has an associated year of value

- Measure 2 has to be multiply by effective rate

- Effective rate is the product of all rates between year of measure2+1 AND year of output :

Knowing I'm then reporting on this with Webi, what is the best way to achieve my requirements ?

Would it be possible to make this calculation directly in the view ?

Would I need to have a new calcultation view with cartesian product between both datasets for the choosen year and then use runningProduct function in webi for the measure 2 and max function on other measures and attributes ?

I tried to build a procedure but I'm stucked :(

the proedure compiles correctly like this :

create procedure effective_rate(IN inval_start integer,inval_end integer,step integer, out val decimal)
-- start is the year of measure to be rated
-- end is the year of output
-- step will always be 1
-- out will give the product of all rates since start+1 to end
language sqlscript
as
begin
declare v_index1 integer;
declare valdec, rate decimal;

v_index1:=inval_start;
valdec:=1;

WHILE :v_index1 <= :inval_end DO
v_index1:=:v_index1+:step; -- first loop = start+1
      
-- retrieve rate for the year 
SELECT 1 FROM "_SYS_BIC"."Analytics.Private.PM/CA_RATES"
WHERE J_1AINDX = 'CA01' and YEAR = v_index1;	

-- apply new rates to product of previous rates
valdec := valdec*rate;
END WHILE;
val:=valdec;
end;

BUT I need now to update rate variable with the returned rate of my SELECT statement...

I tried these 2 codes without success (compilation error...) :

-- retrieve rate for the year 
SELECT 1 FROM "_SYS_BIC"."Analytics.Private.PM/CA_RATES"
WHERE J_1AINDX = 'CA01' and YEAR = v_index1 := rate;	

-- retrieve rate for the year 
rate := SELECT 1 FROM "_SYS_BIC"."Analytics.Private.PM/CA_RATES"
WHERE J_1AINDX = 'CA01' and YEAR = v_index1;	

any help would be appreciate

dataset-1.png (10.2 kB)
dataset-2.png (4.7 kB)
output.png (13.8 kB)
rates.png (5.3 kB)
Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

2 Answers

  • Oct 15, 2018 at 03:49 PM

    hi Aurelien,

    yes it is possible to achieve this from a calculation view. you will need to add the 2 sources into a projection node each.

    then, you will want to join the 2 projections based on the desired year attribute to get the rate information... and create a calculated column for your effective rate calculation. this should be pretty straight fwd.

    additional info: as you build the view, you should be able to see a data preview of the nodes so you can see what each node output you are getting in order to get your desires output.

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 16, 2018 at 03:22 PM

    Hello

    sorry but in the view I cannot find how to make it...

    there are severals lines that cannot agregate with 'product' ony standard agregation (ie sum, max, avg, min...) which are not the one I need...

    as far as I see.. do you have any clue to make it happens

    Add comment
    10|10000 characters needed characters exceeded