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?

[swuehl]

try a

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

then.

Regards,

Stefan

edit: No, this will give you the total accumulated closing, ... wait

=rangesum(above(total column(3)))

[#3]

Alright,

rangesum(above(total column(3)))

Fixes Fiscal period 1 for 2011 however the Closing for Fiscal Period 1 in 2011 is not seeing the previous amounts.

Should I change this too?

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

[swuehl · Accepted solution]

No, I think you should use:

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

for full accumulation of all prior values.

But anyway, just try...

edit:

correct expression is using total two times:

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

(first to cross columns segment bounderies, second to sum up all rows up to the current).

[#5]

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

is giving me the exact same results as

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

[#6]

ok I got it:

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

gives the correct results

[swuehl]

Yes, just looked up the same..

All working now?

[#8]

Yes, thank you so much for your help.Which answer would you like marked as the correct one? If you can edit your last formula I will mark it as the correct answer