Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Hi,
Check attached file for solution.
Regards,
Jagan.
See attachment
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?
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])
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>
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.
sum( Total [PV of Cashflows])
or
sum( Total <Dimensionfield >[PV of Cashflows])
or
sum( Total <Dimensionfield1,Dimensionfield2 >[PV of Cashflows])
hope this helps
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>
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))