Skip to main content
Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
supriyabiware
Creator
Creator

Calculation on Column itself

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

1 Solution

Accepted Solutions
sunny_talwar

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;

Capture.PNG

View solution in original post

17 Replies
YoussefBelloum
Champion
Champion

Hi,

reduced:

LOAD Year,

     %Reduction,

     Apreis,

     Apreis-(Apreis*%Reduction) as Reduced

.

.




attached below


supriyabiware
Creator
Creator
Author

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
Champion
Champion

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

supriyabiware
Creator
Creator
Author

thats the requirement it can be easily done in excel!

YoussefBelloum
Champion
Champion

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

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

supriyabiware
Creator
Creator
Author

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

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;

Capture.PNG

supriyabiware
Creator
Creator
Author

PERFECT !!! Thankyou so much!

supriyabiware
Creator
Creator
Author

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