Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

YTD and Previous year dimensions.... how to do it?

  • 25 April 2019
  • 9 replies
  • 3137 views
P

I’m using postgreSQL, i am trying to add in a new dimension to count the number or orders created, using create_date field, to compare this year to last year. Plus have a YTD count.


How can this be done?


Many thanks,


Paul

9 replies

P

Morning Ben,


Perfect and thanks!

B
Userlevel 5
Badge

Paul, I’m assuming that your ${count} field is a “count” type field. When you want to transform already-aggregated measures (such as count, sum, etc.) with mathematical or logical operations, the way to do so is with a “number”-type measure. Because “count” is already summing up a value, summing it up again yields an error.


The good news is, I think the solution’s pretty straight-forward - just duplicate your count field, and apply the filter there.


measure: count_ytd {

type: count

filters: {

field: is_ytd

value: “yes”

}

}


Hope this works!

Dawid
Userlevel 7
Badge +1

Try typing yes without the double quotes

Dawid
P

Changed the date field in the dimension, it now works… thank you….


This still does not…


measure: total_sales_ytd {


type: sum


sql: ${count}


filters: {


field: is_ytd


value: “yes”


}


label: “Sales Year-to-Date”


}

Dawid
Userlevel 7
Badge +1

Are you sure your commercial_leads.created_on is timestamp/interval and not a text?

Dawid
P

Still does not work….



dimension: is_ytd {


type: yesno


group_label: “Date Restrictions”


label: “Is YTD?”


view_label: “Dynamic Grouping & Time Comparisons”


sql:


EXTRACT (MONTH FROM ${created_on}) < EXTRACT (MONTH FROM CURRENT_TIMESTAMP)


OR


(EXTRACT (MONTH FROM ${created_on}) = EXTRACT (MONTH FROM CURRENT_TIMESTAMP)


AND


EXTRACT (DAY FROM ${created_on}) <= EXTRACT (DAY FROM CURRENT_TIMESTAMP))


;;


}

Dawid
Userlevel 7
Badge +1

For PostgreSQL you’d have to use:


EXTRACT(MONTH FROM CURRENT_TIMESTAMP)
Dawid
P

Hi Ben,


Many thanks for the quick reply.


I have tried adding the below, but it comes up with an error, I think it’s because this is for mysql not postgresql?


Error is as follows…



Any ideas?


Thanks,


Paul

B
Userlevel 5
Badge

Welcome, Paul!


I’m sure it’ll be different in your dialect, but there’s a number of ways to do this. I elected to build a ytd yesno field. This way, a general measure can be filtered by it, and you can also use the filters parameter in another measure to pre-filter on just “yes” if you have pretty regular needs for it.


Hopefully this sets you off on the right foot!


dimension: is_ytd {

 type: yesno

 group_label: "Date Restrictions"

 label: "Is YTD?"

 view_label: "Dynamic Grouping & Time Comparisons"

 sql:

    MONTH(${posting_date}) < MONTH(CURRENT_TIMESTAMP)

    OR

    (MONTH(${posting_date}) = MONTH(CURRENT_TIMESTAMP)

    AND

    DAY(${posting_date}) <= DAY(CURRENT_TIMESTAMP))

    ;;

}



measure: total_sales_ytd {

 type: sum

 sql: ${sales}

 filters: {

  field: is_ytd

  value: "yes"

  }

 label: "Sales Year-to-Date"

}

Reply


Terms of Service