Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Need to use accumulation of another expression in expression.

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?

1 Solution

Accepted Solutions
MVP
MVP

Re: Need to use accumulation of another expression in expression.

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).

16 Replies
vgutkovsky
Honored Contributor II

Re: Need to use accumulation of another expression in expression.

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

MVP
MVP

Need to use accumulation of another expression in expression.

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.

Not applicable

Re: Need to use accumulation of another expression in expression.

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:

YearPeriodOpenClose
20102$0.00$250.00
20108$250.00$774,500.00
20109$774,500.00$162,994.30

This is what I need:

YearPeriodOpenClose
20102$0.00$250.00
20108$250.00$774,750.00
20109$774,750.00$937,774.30
vgutkovsky
Honored Contributor II

Re: Need to use accumulation of another expression in expression.

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

Not applicable

Re: Need to use accumulation of another expression in expression.

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
Esteemed Contributor

Need to use accumulation of another expression in expression.

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)

Not applicable

Re: Need to use accumulation of another expression in expression.

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

MVP
MVP

Re: Need to use accumulation of another expression in expression.

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

Not applicable

Re: Need to use accumulation of another expression in expression.

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