Date Calculation

[Simant_Sah]

Hey guys,

I’ve a requirement wherein I need to exclude the first three weeks from each quarter. Can somebody help me to solve this using a table calculation?

Thanks!

[Cyril_MTL_Analy]

Hmm not sure a table calculation is the way to go here because you would have to add the week dimension to the report and that would mess up your aggregation.

I would use lookML

[Simant_Sah]

Hey Cyril,

Should I write a derived table in the LookML? Or any better method would you like to suggest?

Thanks!

[Cyril_MTL_Analy]

No need for a derived table, a simple yesno dimension that you’ll use as a filter will do:

  dimension: is_excluded_weeks {
    type: yesno
    sql: 
      case
        when ${yourdatedimension_month_num} IN (1,4,7,10) and ${yourdatedimension_day_of_month} <=21 then true
        else false
      end
    ;;
  }

Now bear in mind that filtering on “Yes” for this dimension will only show the excluded weeks (and only the non-excluded weeks when filtering on "No).

If you want a yes/no filter that either filters out the weeks or do nothing then you’ll need a parameter and to change the dimension with a bit of liquid:

  parameter: exclude_first_three_weeks_of_quarters {
    type: yesno
  }
  
  dimension: is_without_excluded_weeks {
    type: yesno
    hidden: yes
    sql: 
      {% if exclude_first_three_weeks_of_quarters._parameter_value == 'true' %}
        case
           when ${yourdatedimension_month_num} IN (1,4,7,10) and ${yourdatedimension_day_of_month} <=21 then false
           else true
        end
      {% else %} true
      {% endif %}
    ;;
   }

Hope that helps!

[Simant_Sah]

Hey Cyril,

It worked perfectly! Thanks for all your help! 🙂

[izzymiller]

A post was split to a new topic: Table calculation - Calculating between two dates - but excluding weekends.