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

(Paul Jeans) #1

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

0 Likes

The Podium — May 1st, 2019
(Ben Silverstein) #2

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"
}
1 Like

(Paul Jeans) #3

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

0 Likes

(Dawid) #4

For PostgreSQL you’d have to use:

EXTRACT(MONTH FROM CURRENT_TIMESTAMP)
0 Likes

(Paul Jeans) #5

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

;;

}

0 Likes

(Dawid) #6

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

0 Likes

(Paul Jeans) #7

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”

}

0 Likes

(Dawid) #8

Try typing yes without the double quotes

0 Likes

(Ben Silverstein) #9

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!

0 Likes

(Paul Jeans) #10

Morning Ben,

Perfect and thanks!

0 Likes