topic Re: Cumulative Sum in Straight Table in QlikView

Cumulative Sum in Straight Table

Thu, 10 Jul 2014 02:02:59 GMT

Hello,

I'd like some help on my problem below:

1. I have a variable "vSlider" that the end user can specify from 1 to 10 on the slider sheet object.

2. I have a table:

Timing	PV of Cashflows
1	100
2	200
3	300
4	400
5	500
6	600
7	700
8	800
9	900
10	1000

3. I'm looking for an expression that can calculate the cumulative sum of "PV of Cashflows" from row 1 to "vSlider." I need it in a single expression because i will do further calculations fromthere.

4. For example

when vSlider = 4,

Timing	PV of Cashflows	Cumulative Sum
1	100	1000
2	200	1000
3	300	1000
4	400	1000

I hope my question is clear. I would really appreciate any help. Thank you.

Re: Cumulative Sum in Straight Table

jagan — Thu, 10 Jul 2014 03:13:17 GMT

Hi,

Check attached file for solution.

Regards,

Jagan.

Re: Cumulative Sum in Straight Table

Anonymous — Thu, 10 Jul 2014 03:50:46 GMT

See attachment

Re: Cumulative Sum in Straight Table

Thu, 10 Jul 2014 04:25:41 GMT

Thank you so much for the fast replies. I have a problem opening the files posted with my QlikView Personal Edition. Would you be so kind to show me the code you wrote for the sheet objects? Thank you very much.

I tried using the TOTAL() qualifier, but I was not able to make it work. Perhaps I also needed to mention that the "Present Value of Cashflows" column is a calculated column in a straight table?

Re: Cumulative Sum in Straight Table

Anonymous — Thu, 10 Jul 2014 05:08:54 GMT

Hi,

See below script,

Dimension - Timing

Expression1 (PV of Cashflows) - Sum ({$<Timing={"<=$(vSlider)"}>} [PV of Cashflows])

Expression2 (Cumulative Sum) - Sum ({$<Timing={"<=$(vSlider)"}>} TOTAL [PV of Cashflows])

Re: Cumulative Sum in Straight Table

Thu, 10 Jul 2014 05:44:18 GMT

Hi,

Thanks for your reply. However, I still cannot get it to work. I understand

what youare doing with that script. My problem is that even when I enter

"=TOTAL[PV of Cashflows]" or sum(PV of Cashflows)

, it does not work. It will only work when I enter:

="PV of Cashflows"

I think the reason to this is because "PV of Cashflows" is a calculated

column, and not a column loaded from a data source.

Regards,

Shintaro

On Thu, Jul 10, 2014 at 2:09 PM, Satyadev Jaiswal <qcwebmaster@qlikview.com>

Re: Cumulative Sum in Straight Table

Anonymous — Thu, 10 Jul 2014 05:56:05 GMT

Hi,

You can't use aggregated function with calculated column. If "PV of Cashflows" is calculated column, you can use the same calculation for Cumulative Sum. For example, I have "Actual Sales" column with Sum(Sales) calculation so my cumulative column will be Sum(TOTAL Sales) not Sum([Actual Sales]).

If you still facing issue, post your logic here. I will try to simplify it.

Re: Cumulative Sum in Straight Table

SunilChauhan — Thu, 10 Jul 2014 06:10:36 GMT

sum( Total [PV of Cashflows])

sum( Total <Dimensionfield >[PV of Cashflows])

sum( Total <Dimensionfield1,Dimensionfield2 >[PV of Cashflows])

hope this helps

Re: Cumulative Sum in Straight Table

Thu, 10 Jul 2014 07:32:54 GMT

Hello,

Thank you so much for your help. I have 2 columns of data "Timing" and

"AllMinuses" both 50 rows in length. I have 9 calculated columnns.

I am trying to get the TOTAL of "PresentValueOfSavingsOnRoyalty" into

"TotalPresentValueofSavingsonRoyalty."

Aside from vSlider1, I also have vRoyaltyRate and vDiscountRate as inputs

from the end user.

Calculated Columns Logic:

BaseOperatingRevenue

=(OperatingRevenue + AllMinuses)*PeriodFactor

PeriodFactor

=if(below(PeriodReference) = 0 and PeriodReference = 1,

below(Timing)-PeriodsRemaining, TimingDiff*PeriodReference)

PeriodReference

if(sum(vSlider1 - Timing)>=0,1,0)

PeriodRemianing

=if(PeriodReference << 1, rangesum(Above(sum(TimingDiff),0,RowNo())) , 0)

SavingsOnRoyalty

=PeriodReference(1-vTaxRate)BaseOperatingRevenue*vRoyaltyRate

DiscountPeriod

=sum(DiscountPeriod)*PeriodReference

DiscountFactor

=Pow((1+vDiscountRate),-1DiscountPeriod)PeriodReference

PresentValueOfSavingsOnRoyalty

=DiscountFactor*SavingsOnRoyalty

TotalPresentValueofSavingsonRoyalty

=Sum(TOTAL PresentValueOfSavingsOnRoyalty)

On Thu, Jul 10, 2014 at 2:57 PM, Satyadev Jaiswal <qcwebmaster@qlikview.com>

Re: Cumulative Sum in Straight Table

Anonymous — Thu, 10 Jul 2014 07:46:32 GMT

Hi,

Try below, I am not sure if it will work. Just multiplying SavingsOnRoyalty and DiscountFactor logic together with TOTAL element.

Sum (TOTAL (Pow((1+vDiscountRate),-1DiscountPeriod)PeriodReference)*(PeriodReference(1-vTaxRate)BaseOperatingRevenue*vRoyaltyRate))