Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Sum up to a date using Set Analysis in a Chart Table - need help please!

Hi,

I have a table of credit and debit movements for monthly periods and I need a balance column. 

I tried originally with an If statement which works find to give the total movements within that period, but because the EndOfPeriod is a dimension in the table, this will not show an accumulated balance over a whole year. 

Sum(if(TransactionDate <= EndOfPeriod,BaseCredit,0)) - Sum(if(TransactionDate <= EndOfPeriod,BaseDebit,0))

I'm pretty sure Set Analysis should be able to answer my questions but I can't figure out how to make it work.  I've tried the below (and a few variations on a theme) but have come up with nothing that gives me what I need.

Sum({<TransactionDate = {"<=EndOfPeriod"}>} BaseCredit)-Sum({<TransactionDate = {"<=EndOfPeriod"}>} BaseDebit)

Please can anyone help?

Many thanks,

Emma

6 Replies
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

     Why dont you create a field in your script which is nothing but the Cr-Dr.

     Then you can just sum up to get the balance as of date.

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Not applicable
Author

Hi Kaushik,

Not really sure I understand how that would work.

My original data looks something like this:

Account     TransactionDate     EndOfPeriod     BaseCredit     BaseDebit

ex123          03-04-2012          30-04-2012          0                         150

ex123          06-04-2012          30-04-2012          2500                    0

as456          21-04-2012          30-04-2012          250                      0

But with about 50 account codes and transaction dates ranging from March 2010 to April 2012.  I need to show an end of period balance, for each period and for each account code, that is a sum of credits-debits from March 2010 until the end of period date. 

I can get a total balance using ALL or I can get an end of period balance that just calculates movement within a period, what I am missing is the end of period balance that goes from March 2010 to the end of period.

Thanks,

Emma

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

     Emma, tell me one thing.

     Do you mean you want to show the chart which will have month as dimension and the balance as of last day of that month?

Regards,

Kaushik Solanki         

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Gysbert_Wassenaar

If you're using EndOfPeriod as dimension in your chart then read this document: Calculating rolling n-period totals, averages or other aggregations


talk is cheap, supply exceeds demand
Not applicable
Author

Hi Gysbert,

Really helpful doc - now if I can figure out how to make it work over 25 months (the time period that my data covers) and aggregate on account code as well as month that would be ideal.

Gradually getting there but getting some funny answers when I add the account code into the aggregation.

Thanks,

Emma

Not applicable
Author

Hi Kaushik,

Yes - I need to see both the month dimension and the balance as of the last day of the month - but it is an opening date (March-2010) up to the month balance which is causing the problems. 

Thanks,

Emma