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: 
tommyl
Creator
Creator

Opening Balance

Hello experts,

I am trying to create a balance report. I need to state the opening balance as a row. In the chart expression i wrote: 

RangeSum(Sum([amount]),-Above(Sum({<[createdat.autoCalendar.Month], [createdat.autoCalendar.Year]>}fabs([amount])))
) *
Avg(1) 

I have Month and Year dimensions in the pivot table as attached:

tommyl_0-1597171349763.png

Despite before 2019 December there is no transaction, the opening balance is not zero.

Could you please help me on that?

Regards,

Tommy.

 

1 Solution

Accepted Solutions
Gabriel
Partner - Specialist III
Partner - Specialist III

Hi,

wrote a blog post a while back and I think it might help you. See link below

Opening balance 

View solution in original post

7 Replies
tommyl
Creator
Creator
Author

Hello,

i also tried this too:

Sum({$<[createdat]={"$(=Date(AddMonths(Max([createdat]),-1),'YYYY-MM'))"}>}[amount])

This didnot work either. 

tommyl
Creator
Creator
Author

I also tried this: 

rangesum(Above( sum(amount]) , 1, rowno()))

 

but it works only within the month not in the "opening balance" row and i can only see this when i add createdat as a dimension to the table. .  

tommyl_0-1597259035304.png

 

And tried this:

RangeSum(Above(Total Sum({<[createdat.autoCalendar.Year]=,[createdat.autoCalendar.Month]=>}([amount])),0,RowNo(TOTAL))) * Avg(1) but first value does not turn out to be zero:

tommyl_0-1597259551412.png

 

Any guide/clue/help really appreciated. 

 

sunny_talwar

What exactly are you hoping to get?

Gabriel
Partner - Specialist III
Partner - Specialist III

Hi,

wrote a blog post a while back and I think it might help you. See link below

Opening balance 

tommyl
Creator
Creator
Author

Hello Sunny,

I want to derive below content in different format:

tommyl_0-1597319995199.png

What i am trying to display is: 

tommyl_1-1597320218503.png

Columns in the rows and rows in the columns. 

Is there a way?

Thank you,

T. 

tommyl
Creator
Creator
Author

Hello Gabriel,

I changed the display of the pivot since your Cartesian problem in the blog directed me so. 

It worked thank you. 

Thank you,

Tommy

Gabriel
Partner - Specialist III
Partner - Specialist III

Hi,

Am glad it worked for you.