Question

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

  • 25 April 2019
  • 9 replies
  • 3019 views

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

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"

}

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

Userlevel 7
Badge +1

For PostgreSQL you’d have to use:



EXTRACT(MONTH FROM CURRENT_TIMESTAMP)

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))



;;



}

Userlevel 7
Badge +1

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

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”



}

Userlevel 7
Badge +1

Try typing yes without the double quotes

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!

Morning Ben,



Perfect and thanks!

Reply