Discussion board where members can get started with QlikView.
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?
Solved! Go to 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).
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
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.
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 |
Like I said, use rangesum(above(column(3))) instead of the expression you have.
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))
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)
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
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
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