Can we ignore zero from Dynamic Tier

(Rohini Isarapu) #1

I have created a dynamic tier by following the link: Creating Dynamic Tiers

However, I want to ignore 0 or start my dynamic tier with 1.

Please can anybody help!!

0 Likes

(Menashe Hamm) #2

One easy way to use buckets 1-10, 11-20, etc., instead of 0-9, 10-19, etc., is to use the ceiling function for your SQL dialect instead of its floor function (in the SQL linked to above).

0 Likes

(Rohini Isarapu) #3
dimension:  MEASURE_raw  {
    type: number
    group_label: "MEASURE"
    hidden: no
    sql: ${TABLE}.MEASURE
  }

parameter: MEASURE_bucket_size {
    default_value: "2"
    type: number
  }

  dimension: MEASURE_dynamic_bucket  {
    description: "tiers of MEASURE"
    group_label: "MEASURE"
    sql:
    cast(
    ROUND(${MEASURE_raw}/ {% parameter MEASURE_bucket_size %}, 0) * {% parameter MEASURE_bucket_size %}
    as varchar(256)
    )
    +'-'+
    cast(
    ROUND(${MEASURE_raw}/ {% parameter MEASURE_bucket_size %}, 0) * {% parameter MEASURE_bucket_size %}
    + {% parameter MEASURE_bucket_size %}
    as varchar(256)
    )
    +'%'

    ;;
    order_by_field: MEASURE_bucket_sort
  }

  dimension: MEASURE_bucket_sort {
    group_label: "MEASURE"
    sql:
      ROUND(${MEASURE_raw}/ {% parameter MEASURE_bucket_size %}, 0) * {% parameter MEASURE_bucket_size %};;
    type: number
    hidden: no
  }

I used these dimensions and measures. Please can you let me know where to add the ceiling function?

0 Likes

(Menashe Hamm) #4

This is dialect-dependent, but in theory instead of

cast(
    ROUND(${MEASURE_raw}/ {% parameter MEASURE_bucket_size %}, 0) * {% parameter MEASURE_bucket_size %}
    as varchar(256)
    )
    +'-'+
    cast(
    ROUND(${MEASURE_raw}/ {% parameter MEASURE_bucket_size %}, 0) * {% parameter MEASURE_bucket_size %}
    + {% parameter MEASURE_bucket_size %}
    as varchar(256)
    )
    +'%'

you’d have

cast(
    ceiling(${MEASURE_raw}/ {% parameter MEASURE_bucket_size %}) * {% parameter MEASURE_bucket_size %}
    - {% parameter MEASURE_bucket_size %}
    as varchar(256)
    )
    +'-'+
    cast(
    ceiling(${MEASURE_raw}/ {% parameter MEASURE_bucket_size %}) * {% parameter MEASURE_bucket_size %}
    as varchar(256)
    )
    +'%'

if ${MEASURE_raw} is always positive.

1 Like