Need help with data formats in set analysis formula

[Anonymous]

Hi all,

I have a set analysis formula that is used to get the trend for last 12 months data.However, I am unable to convert these to number or percent formats. I have field 'data type' which has the data type (number or percent) for the selected KPI. I used the below formula to convert the data formats. But gives me only the un-formatted (only the number format) results.

KPI_Trend =if (DataType = 'Percent', Num( Avg({1<PeriodType = p(PeriodType), EntityName = p(EntityName), MeasureName = p(MeasureName) ,MeasureDomain = p(MeasureDomain), PeriodKey_End_Date = {"$(='>' & Date(AddMonths(Max(PeriodKey_End_Date), -12), 'YYYY-MM-DD') & '<=' & Date(AddMonths(Max(PeriodKey_End_Date),1), 'YYYY-MM-DD'))"} >} KPI_Value),'##0.00%'),

  if( DataType = 'Number', Num(Avg({1<PeriodType = p(PeriodType), EntityName = p(EntityName), MeasureName = p(MeasureName) ,MeasureDomain = p(MeasureDomain), PeriodKey_End_Date = {"$(='>' & Date(AddMonths(Max(PeriodKey_End_Date), -12), 'YYYY-MM-DD') & '<=' & Date(AddMonths(Max(PeriodKey_End_Date),1), 'YYYY-MM-DD'))"} >} KPI_Value),'###0.00') ,

   Avg({1<PeriodType = p(PeriodType), EntityName = p(EntityName), MeasureName = p(MeasureName) ,MeasureDomain = p(MeasureDomain), PeriodKey_End_Date = {"$(='>' & Date(AddMonths(Max(PeriodKey_End_Date), -12), 'YYYY-MM-DD') & '<=' & Date(AddMonths(Max(PeriodKey_End_Date),1), 'YYYY-MM-DD'))"} >} KPI_Value)

  ))

Tried the below way as well,but no luck. Created 2 variables and used these in the formula.

vKPI_Percent =   Num(Avg({1<PeriodType = p(PeriodType), EntityName = p(EntityName), MeasureName = p(MeasureName) ,MeasureDomain = p(MeasureDomain), PeriodKey_End_Date = {"$(='>' & Date(AddMonths(Max(PeriodKey_End_Date), -12), 'YYYY-MM-DD') & '<=' & Date(AddMonths(Max(PeriodKey_End_Date),1), 'YYYY-MM-DD'))"} >} KPI_Value),'##0.00%' )

vKPI_Number =  Num(Avg({1<PeriodType = p(PeriodType), EntityName = p(EntityName), MeasureName = p(MeasureName) ,MeasureDomain = p(MeasureDomain), PeriodKey_End_Date = {"$(='>' & Date(AddMonths(Max(PeriodKey_End_Date), -12), 'YYYY-MM-DD') & '<=' & Date(AddMonths(Max(PeriodKey_End_Date),1), 'YYYY-MM-DD'))"} >} KPI_Value),'##0.00' )

vKPI_selected =  if (DataType = 'Percent', $(vKPI_Percent), $(vKPI_Number)

Any suggestions, please  ?

[Anonymous]

Hi,

Can you use the NUM() function without using it in a variable