I have run into an issue using the NUM() formatting Function in pair with set analysis.
This is my Expression:
=num( sum(
{$< [Fiscal Period] = {"<=$(=Max([Fiscal Period]))"}>}
(if([Fiscal Period]=0,0,[Credit Amount] + [Debit Amount]) +[Opening Balance])),
if([Next Parent]<>'Assets','($#,##0.00);$#,##0.00','$#,##0.00;($#,##0.00)'))
This expression is showing for a balance sheet. To get the correct balances to show, I need to pull the information for all periods before the selected period. I obtain this using the above set analysis
{$< [Fiscal Period] = {"<=$(=Max([Fiscal Period]))"}>}
Now I also for display purposes, was the ending summary to display as a positive or negative based on the field [Next Parent].
My issue is that when a user selects period 2 (therefor for this chart pulling data for periods 0-2) the number function no longer works correctly. I have found that this is caused by an account not having any records under period 2, but having records under one of the previous periods. Since the number function is not included in the set, the value is displayed incorrectly. That I know of there is no set analysis usable on a formation field so I am not sure what to do here.
