topic Re: Using previous cell of calculated field to generate the new value in same field in App Development

Using previous cell of calculated field to generate the new value in same field

Qliksense_User — Mon, 20 Dec 2021 21:08:46 GMT

I am developing a report as above where my pos is getting calculated in the chart. Basically my POS for FEB is POS for Jan-Principal for Feb and for DEC 2019 it is default value of POS/1e7. So i have used if( amort_monthyear_name='Dec 2019',column(1) , before(column(1))-column(2)). As you can see in the screenshot it is working fine for Jan i.e. 3133.44-68.81=3064.63 whereas for FEB it is doing 3429.33-138.78=3290.55 as i have used before(column(1)) and my requirement is it should do derived POS value of Jan 3064.63- 138.78=2925.85 same for March would be 2925.85-136.27=2789.58

I have also tried to get previous month POS using set analysis by Sum({<amort_monthyear_name={"$(vPreviousMonth)"}>}Pos)/10000000 where vPreviousMonth is my variable for previous month.

The Problem here is when is use this set in KPI I get the exact value however when is use this in pivot table i get 0 values.

Thanks in advance for the help

Re: Using previous cell of calculated field to generate the new value in same field

IamAlbinAntony — Wed, 06 May 2020 06:25:14 GMT

In front end use RangeSum() function for doing accumulation.

Re: Using previous cell of calculated field to generate the new value in same field

Qliksense_User — Wed, 06 May 2020 06:49:35 GMT

Thanks FruzoCrew i already tried

if( amort_monthyear_name='Dec 2019',Sum(Pos)/10000000,RangeSum(Above(column(3)))-column(1)) and was able to achieve the desired figures in a straight table however, the same expression is not working in a pivot table, I believe maybe RangeSum() would not work in pivot table.

Thanks once again FruzoCrew😊

Re: Using previous cell of calculated field to generate the new value in same field

IamAlbinAntony — Wed, 06 May 2020 07:08:12 GMT

Try this;

if( amort_monthyear_name='Dec 2019',Sum(Pos)/10000000,RangeSum(before(column(3)))-column(1))