Announcements
You can succeed best and quickest by helping others to succeed. Join the conversation.
cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## Set Analysis and the NUM function

Hi there,

I am using a variable to return a result.  in the variable it uses set analysis to ensure that regardless of what fiscalyear is selected the result for ALL years will show.

=NUM(\$(vResultAllYears),NumberFormat)

The next thing I do is use a field to determine the number format.  In the instance below it should be formatted as '#.##%'.

As you can see from the table below, when a single fiscalyear (2011-12) is selected, all values are displayed but the NumberFormat is only applied to the selected range.

How can I overcome this?  Keeping in mind that depending on the indicator being displayed this number format will change.

see attached sample.

Thanks,

John.

1 Solution

Accepted Solutions
MVP

May be this:

IF(Only({\$ <FiscalYear = >} CalculationRule)='N/D'

,SUM({\$ <FiscalYear = >} Numerator)/SUM({\$ <FiscalYear = >} Denominator)

,IF(Only({\$ <FiscalYear = >} CalculationRule) ='(N/D)/R'

,(SUM({\$ <FiscalYear = >} Numerator)/SUM({\$ <FiscalYear = >} Denominator)/Rate)

,IF(Only({\$ <FiscalYear = >} CalculationRule) ='(N/D)/100'

,(SUM({\$ <FiscalYear = >} Numerator)/SUM({\$ <FiscalYear = >} Denominator)/100)

,IF(Only({\$ <FiscalYear = >} CalculationRule) ='(N/D)/1000'

,(SUM({\$ <FiscalYear = >} Numerator)/SUM({\$ <FiscalYear = >} Denominator)/1000)

,IF(Only({\$ <FiscalYear = >} CalculationRule) ='(N/D)*R'

,(SUM({\$ <FiscalYear = >} Numerator)/SUM({\$ <FiscalYear = >} Denominator)*Rate)

,IF(Only({\$ <FiscalYear = >} CalculationRule) ='(N/D)*100'

,(SUM({\$ <FiscalYear = >} Numerator)/SUM({\$ <FiscalYear = >} Denominator)*100)

,IF(Only({\$ <FiscalYear = >} CalculationRule) ='(N/D)*1000'

,(SUM({\$ <FiscalYear = >} Numerator)/SUM({\$ <FiscalYear = >} Denominator)*1000)

)))))))

8 Replies
MVP

Try this expression:

=NUM(\$(vResultAllYears), Only({\$ <FiscalYear = >}NumberFormat))

Anonymous
Not applicable
Author

Hi Sunindia,

Thank you, that solution works for this situation.

Unfortunately I was being a little bit simple in my calculation in the variable vResultsAllYears and instead of looking like this:

SUM({\$ <FiscalYear = >} Numerator)/SUM({\$ <FiscalYear = >} Denominator)

it actually looks like this.  For some reason regardless of the set analysis it only displays a result for the selected year:

IF(CalculationRule='N/D'

,SUM({\$ <FiscalYear = >} Numerator)/SUM({\$ <FiscalYear = >} Denominator)

,IF(CalculationRule='(N/D)/R'

,(SUM({\$ <FiscalYear = >} Numerator)/SUM({\$ <FiscalYear = >} Denominator)/Rate)

,IF(CalculationRule='(N/D)/100'

,(SUM({\$ <FiscalYear = >} Numerator)/SUM({\$ <FiscalYear = >} Denominator)/100)

,IF(CalculationRule='(N/D)/1000'

,(SUM({\$ <FiscalYear = >} Numerator)/SUM({\$ <FiscalYear = >} Denominator)/1000)

,IF(CalculationRule='(N/D)*R'

,(SUM({\$ <FiscalYear = >} Numerator)/SUM({\$ <FiscalYear = >} Denominator)*Rate)

,IF(CalculationRule='(N/D)*100'

,(SUM({\$ <FiscalYear = >} Numerator)/SUM({\$ <FiscalYear = >} Denominator)*100)

,IF(CalculationRule='(N/D)*1000'

,(SUM({\$ <FiscalYear = >} Numerator)/SUM({\$ <FiscalYear = >} Denominator)*1000)

)))))))

Would you know how to force this to display across all years?

Thank you again.

Master III

try this also

=NUM(\$(vResultAllYears),'\$(=NumberFormat)')

Anonymous
Not applicable
Author

Settu_periasamy,

that is a very interesting solution and it also works.  But what is it doing?

MVP

May be this:

IF(Only({\$ <FiscalYear = >} CalculationRule)='N/D'

,SUM({\$ <FiscalYear = >} Numerator)/SUM({\$ <FiscalYear = >} Denominator)

,IF(Only({\$ <FiscalYear = >} CalculationRule) ='(N/D)/R'

,(SUM({\$ <FiscalYear = >} Numerator)/SUM({\$ <FiscalYear = >} Denominator)/Rate)

,IF(Only({\$ <FiscalYear = >} CalculationRule) ='(N/D)/100'

,(SUM({\$ <FiscalYear = >} Numerator)/SUM({\$ <FiscalYear = >} Denominator)/100)

,IF(Only({\$ <FiscalYear = >} CalculationRule) ='(N/D)/1000'

,(SUM({\$ <FiscalYear = >} Numerator)/SUM({\$ <FiscalYear = >} Denominator)/1000)

,IF(Only({\$ <FiscalYear = >} CalculationRule) ='(N/D)*R'

,(SUM({\$ <FiscalYear = >} Numerator)/SUM({\$ <FiscalYear = >} Denominator)*Rate)

,IF(Only({\$ <FiscalYear = >} CalculationRule) ='(N/D)*100'

,(SUM({\$ <FiscalYear = >} Numerator)/SUM({\$ <FiscalYear = >} Denominator)*100)

,IF(Only({\$ <FiscalYear = >} CalculationRule) ='(N/D)*1000'

,(SUM({\$ <FiscalYear = >} Numerator)/SUM({\$ <FiscalYear = >} Denominator)*1000)

)))))))

Master III

yes the \$ symbol will do that.  🙂

It will evaluate the inside of the value. See this if you are interested..

The Magic of Dollar Expansions

Anonymous
Not applicable
Author

Its amazing.  I've never seen an expression like this before.

Anonymous
Not applicable
Author

Thank you both.  This has been a valuable experience for me.

Community Browser