When date is filtered, opening balance turns out to be zero not the previous year's

[tommyl]

Hi,

I created a balance report like:

The problem: When i select filter value as 2020 , opening balance of 2020-Jan does not displaythe closing balance of 2019-Dec: 

What is expected: 2020-Jan should have the value 70,998 instead of 0(zero). 

Data used are:

Filter: [createdat.autoCalendar.Year]

Opening Balance Dimension: rangesum(Above(sum({<[category]={'STANDARD'}>*<[subtype]={'CREATION'}>}fabs([amount])) , 1, rowno()))*Avg(1)

Closing Balance Dimension: rangesum(Above(sum({<[category]={'STANDARD'}>*<[subtype]={'CREATION'}>}fabs([amount])) , 0, rowno()))

Could you please help me on this? 

Regards,

T. 

[chrismarlow · Accepted solution]

Hi,

I think you need to cancel your Year date selection in your set analysis, also include *Avg(1) on the closing balance & make sure to deselect Include zero values (in Add-ons-Data Handling);

rangesum(Above(sum({<Year,[category]={'STANDARD'},[subtype]={'CREATION'}>}fabs([amount])) , 1, rowno()))*Avg(1)

rangesum(Above(sum({<Year,[category]={'STANDARD'},[subtype]={'CREATION'}>}fabs([amount])) , 0, rowno()))*Avg(1)

Let me know if that helps.

Cheers,

Chris.

[tommyl]

Hi Chris

Thank you, it perfectly worked 🙂 

Regards,