Set Analysis and the NUM function

[Anonymous]

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.

[sunny_talwar]

Try this expression:

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

[Anonymous]

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.

[settu_periasamy]

try this also

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

[Anonymous]

Settu_periasamy,

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

[sunny_talwar · Accepted solution]

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)

)))))))

[settu_periasamy]

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]

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

[Anonymous]

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