Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
Partner
Partner

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
Highlighted
Creator
Creator

Hello,

i also tried this too:

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

This didnot work either. 

Highlighted
Creator
Creator

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. 

 

Highlighted

What exactly are you hoping to get?

Highlighted
Partner
Partner

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

Highlighted
Creator
Creator

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. 

Highlighted
Creator
Creator

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

Highlighted
Partner
Partner

Hi,

Am glad it worked for you.