Question

Dynamic named_value_Format

  • 23 November 2021
  • 0 replies
  • 3 views

is there anyway to make this code work from this thread: 

 

I’m trying to dynamically change the measure value_format based on the value of a parameter. but I keep getting error trying to use the suggestion in attached thread. 

this is my code: 


parameter: Metric_Selection {
type: string
description: "Parameter for setting up breakdown"
allowed_value: { value: "Clicks" }
allowed_value: { value: "Impressions" }
allowed_value: { value: "Cost" }
allowed_value: { value: "CTR" }
allowed_value: { value: "CPC" }
allowed_value: { value: "CVR" }
allowed_value: { value: "CPA" }
allowed_value: { value: "Revenue" }
allowed_value: { value: "ROAS" }
allowed_value: { value: "Conversion" }

}


dimension: format_dynamic {
sql: if({% parameter Metric_Selection %} = "Clicks" ,"#,##0.00",
if({% parameter Metric_Selection %} = "Impressions" ,"#,##0.00",
if({% parameter Metric_Selection %} = "Cost","$#,##0.00",
if({% parameter Metric_Selection %} = "CTR","0.00\\%",
if({% parameter Metric_Selection %} = "CPC","$#,##0.00",
if({% parameter Metric_Selection %} = "CVR","0.00000\\%",
if({% parameter Metric_Selection %} = "CPA","$#,##0.00",
if({% parameter Metric_Selection %} = "ROAS","0.00\\%",
if({% parameter Metric_Selection %} = "Conversion","#,##0.00","#,##0.00")))))))));;

}



measure: final_metrics {
label_from_parameter: Metric_Selection
group_label: "Transaction_Info"
label: "Transaction_Info"
description: "Select your desired metric"
sql:
CASE
WHEN {% parameter Metric_Selection %} = "Clicks" THEN sum(${TABLE}.Clicks)
WHEN {% parameter Metric_Selection %} = "Impressions" THEN sum(${TABLE}.Impressions)
WHEN {% parameter Metric_Selection %} = "Cost" THEN sum(${TABLE}.Cost)
WHEN {% parameter Metric_Selection %} = "Revenue" THEN sum(${TABLE}.total_rev)
WHEN {% parameter Metric_Selection %} = "CTR" THEN case when sum(impressions)=0 then 0 else sum(clicks)/sum(impressions) end
WHEN {% parameter Metric_Selection %} = "CPC" THEN case when sum(clicks)=0 then 0 else sum(cost)/sum(clicks) end
WHEN {% parameter Metric_Selection %} = "CVR" THEN case when sum(clicks)=0 then 0 else sum(${TABLE}.overall_conversions)/sum(clicks) end
WHEN {% parameter Metric_Selection %} = "CPA" THEN case when sum(${TABLE}.overall_conversions)=0 then 0 else sum(cost)/sum(${TABLE}.overall_conversions) end
WHEN {% parameter Metric_Selection %} = "ROAS" THEN case when sum(${TABLE}.cost)=0 then 0 else sum(${TABLE}.total_rev)/sum(cost)*100 end
WHEN {% parameter Metric_Selection %} = "Conversion" THEN sum(${TABLE}.overall_conversions)
ELSE NULL
END ;;
}

is there anyway to enforce the format_dynamic to my measure final_metrics? 


0 replies

Be the first to reply!

Reply