Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for Data Integration and Data Analytics gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Need help with data formats in set analysis formula

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  ?


1 Reply
Highlighted
Partner
Partner

Re: Need help with data formats in set analysis formula

Hi,

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