Need help with data formats in set analysis formula

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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 ?

Re: Need help with data formats in set analysis formula

Anonymous — Mon, 13 Mar 2017 12:45:40 GMT

Hi,

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

topic Need help with data formats in set analysis formula in QlikView

Need help with data formats in set analysis formula

Re: Need help with data formats in set analysis formula