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!
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
Hey Cyril,
Should I write a derived table in the LookML? Or any better method would you like to suggest?
Thanks!
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!
Hey Cyril,
It worked perfectly! Thanks for all your help! 🙂
A post was split to a new topic: Table calculation - Calculating between two dates - but excluding weekends.