Calculation on Column itself

[supriyabiware]

Hi All,

I have a small requirement as below. Can anybody help in this?

You can suggest any script calculation or Set analysis (i prefer script calculation)

i want to reduce column APREIS depending on % Reduction - Expected result is as below...

Note - Comma is decimal point in Last column

Thanks

Supriya

[YoussefBelloum]

Hi,

reduced:

LOAD Year,

     %Reduction,

     Apreis,

     Apreis-(Apreis*%Reduction) as Reduced

.

.

attached below

[supriyabiware]

Thanks youseff for your reply.

i have a slight twist here..

i need to reduced the APREIS with already reduced apreis from previous Year.

Attached is the sample .

[YoussefBelloum]

How can i Know the already reduced amounts last year ? to reduce them

[supriyabiware]

thats the requirement it can be easily done in excel!

[YoussefBelloum]

when i say how, I'm talking with the sample data you provided.

there is no indication here.. what is the calculation rule ??

[supriyabiware]

Considering Headers as row 1 i have applied below formula in column N

1st row -    M2*(1-L2)

2nd row -    N2*(1-L3)

3rd row -     N3*(1-L4)

4th row -     N4*(1-L5)

.....so on

[sunny_talwar · Accepted solution]

May be this

Table:

LOAD * INLINE [

    Year, % Reduction, Apreis

    2016, 0, 1231

    2017, 0, 1231

    2018, 0.03, 1231

    2019, 0.03, 1231

    2020, 0.03, 1231

    2021, 0, 1231

    2022, 0, 1231

    2023, 0, 1231

    2024, 0, 1231

    2025, 0, 1231

    2026, 0, 1231

    2027, 0, 1231

];

TempTable:

LOAD *,

Alt(Peek('% Reduction'), 0) as [New % Reduction]

Resident Table

Order By Year Desc;

Final:

LOAD *,

[New % Reduction] * [Reduced APREIS] as Reduction;

LOAD *,

RangeSum(Alt(Peek('Reduced APREIS'), [Apreis]), -Alt(Peek('Reduction'), 0)) as [Reduced APREIS]

Resident TempTable

Order By Year;

DROP Table Table, TempTable;

[supriyabiware]

PERFECT !!! Thankyou so much!

[supriyabiware]

i have one more thing to do here.. i have to do this calculation for one set of IDs. so i did this

if(ID=Previous(ID), RangeSum(Alt(Peek('REDUCED_APREIS'), APREIS_1), -Alt(Peek('TEMP'),0))) as REDUCED_APREIS

But something is not working here -i am getting blank for first row..

Help appreciated thanks