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 ?
