[Need HELP]Achieving the requirement in pivot table

[prashanthd]

Hi All,

i would like to know the possiblity of achieving the requirement in front end.

I am struggling with calculating the opening balance and closing balance since it is very dynamic.

Initial opening balance = 20

Opening Balance = last week closing balance

Closing Balance = Opening Balance + New PR Added  - PR Coverted  - PR Deleted

last week closing balance is next week opening balance and so on 

QVF Attached

[SRA · Accepted solution]

Hi,

In opening Balance :

RangeSum( Before( (Count( {<Flag_Date = {'PR'}>} distinct [PR No]) - Count({<Flag_Date = {'PO'}>} distinct [PR No]) - Count({<Flag_Date = {'PR Deleted'}>} distinct [PR No])), 1, ColumnNo()) )

In Closing balance : 

RangeSum( Before( (Count( {<Flag_Date = {'PR'}>} distinct [PR No]) - Count({<Flag_Date = {'PO'}>} distinct [PR No]) - Count({<Flag_Date = {'PR Deleted'}>} distinct [PR No])), 1, ColumnNo()) )
+ Count({<Flag_Date = {'PR'}>} distinct [PR No]) - Count({<Flag_Date = {'PO'}>} distinct [PR No]) - Count({<Flag_Date = {'PR Deleted'}>} distinct [PR No])

You will get this :

Regards

SRA

[prashanthd]

Hi @SRA ,

Thank you for your answer. This works but problem starts when i filter for certain weeks. My requirement is opening balance and closing balance  should not change  on week selection.

[prashanthd]

Thank you @SRA the issue is resolved by adding week exclusion inside set and multiplying the rangesum with Avg(1) to bring back the selection.

Also had to uncheck "Include NULL Values" in the addon's