Need to use accumulation of another expression in expression.

[#1]

I have an issue. I was able to get the results I want on crystal using a running total. I am now trying to recreate this is Qlikview, but I am running into issues.

I am not sure how to use running totals in qlikview, so currently the following formula is returning 0 since I do not know how to tell it that I need the running total amount.

Should be the accumulated total of the balance minus the current fiscal periods balance.

Qlikview formula for Opening balance (Should be previous periods closing balance):

(if (sum(balanceamt-openbalance)=0 ,  sum(balanceamt) , sum((balanceamt-openbalance)- (balanceamt))))

Crystal Formula:

if {#RTotal1}=0 then

Sum ({BAQReportResult.GLPeriodBal.BalanceAmt}, {BAQReportResult.GLPeriodBal.FiscalPeriod})

else {#RTotal1}- Sum ({BAQReportResult.GLPeriodBal.BalanceAmt}, {BAQReportResult.GLPeriodBal.FiscalPeriod})

{#RTotal1}:

{BAQReportResult.GLPeriodBal.BalanceAmt}-{BAQReportResult.GLPeriodBal.OpenBalance}

If this is not clear, I have a straight table with dimensions Fiscal Year and Fiscal Period.

Opening balance is defined as the accumulation total of the balance at that point minus the current periods balance

Closing Balance is the accumulated total of the balance at that point

Current balance is the sum of the balance for only that period

Ex:

Year  Period  open balance  current balance  Closing Balance

2010 1          0                      250                      250

2010 2          250                  774,500               774,750    

2010 3          774,750            937,774              162,994               

Edit:

Is there a way to use set analysis to take the sum of all values less than the current dimension value of the table?

[vgutkovsky]

When using Set Analysis, keep in mind that you can't use it to check what dimension you're currently in--i.e. set analysis is calculated once for the entire chart and can be used to limit dimensions rather than manipulate them. To create this table, you would need 3 expressions:

Expression #1 (open balanace): rangesum(above(column(3)))

     I'm enclosing the sum in a rangesum just in case your result is null.

Expression #2 (current balance): sum(CurrentBalance)

Expression #3: column(2) + column(1)

Regards,

Vlad Gutkovsky

[swuehl]

Hi,

I think you could use chart inter records for that, like

= above(CLOSINGBALANCEEXPRESSION)

for open balance ( you might need to handle the first row e.g. by checking for NULL)

Regards,

Stefan

P.S. I think you can't use set expression for your edit request. set epression will not take care of the current dimension value.

[#4]

So It would be something similar to the following expression where in place of the running total I use rangesum(above())?

if(rangesum(above(balanceamt-openbalance))=0,sum(balanceamt),rangesum(above(balanceamt-openbalance))-sum(balanceamt))

Edit:

Ok looking at the results side by side,

rangesum(above(balanceamt-openbalance)) as opening

sum(balanceamt) as closing

is showing the previous periods closing balance as the current periods opening. This is closer to what I was looking for, except I need the closing balance to be acumulated down the entire table. So this is what I currently have:

Year	Period	Open	Close
2010	2	$0.00	$250.00
2010	8	$250.00	$774,500.00
2010	9	$774,500.00	$162,994.30

This is what I need:

Year	Period	Open	Close
2010	2	$0.00	$250.00
2010	8	$250.00	$774,750.00
2010	9	$774,750.00	$937,774.30

[vgutkovsky]

Like I said, use rangesum(above(column(3))) instead of the expression you have.

[#6]

What should the formula be for column(3) then? I need column(3) to be a running total. that running total is then used in the formula that you posted.

So rangesum(above(column(3))) is giving me the same results as rangesum(above(balanceamt-openbalance))

[SunilChauhan]

in dimension Year and period

expression

1. Sum(open)

2below(column(1)

column(1) is nothing but its denoting first expression i.e Sum(open)

[#8]

My big issue is that for this table it only records the balance for that fiscal period. So in order to get the correct opening/closing balances, I need to use the previous periods balances.

So to get say period 3's correct closing balance, I need to add together all period balances <=period 3.

Therefor period 3's opening balance would be the sum of all balances <period 3

[swuehl]

try using

=rangesum(above(sum(balanceamt),0,rowno()))

as accumulated closing.

Regards,

Stefan

P.S: and as Vlad suggested

=rangesum(above(column(3)))

for opening

[#10]

Ok this is working magically. However, it is resetting itself when the fiscal year changes.

The ending balance of period 12, 2010 should be the opening balance of period 1, 2011. Right now with this formula I am getting 133,562 closing 12, 2010 and 0 opening 1, 2011