How to create QTD chart with QTD change in Looker with BQ

[rohit_shoppertr]

Hi team,

I am trying to create QTD period over period analysis by which this kind of chart would be there:

Can you please guide me how to create a chart for latest Quarter up to latest date vs. last quarter same day.

For example today is 5th day of this quarters so 

5 days sales of this quarter/5 days of last completed quarter

Thanks in advance

Rohit

[Dawid]

I use the following concept:

current point in time - how many seconds passed from the beginning of the quarter?

I call it current_second_of_quarter

  dimension: second_of_day {
    group_label: "Current"
    type: number
    sql: (EXTRACT(HOUR FROM CURRENT_TIMESTAMP) * 60 * 60) + (EXTRACT(MINUTE FROM CURRENT_TIMESTAMP) * 60) + EXTRACT(SECOND FROM CURRENT_TIMESTAMP) ;;
  }

  dimension: second_of_quarter {
    group_label: "Current"
    sql: (${second_of_day} + ((TIMESTAMP_DIFF(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP, DAY), TIMESTAMP_TRUNC(CURRENT_TIMESTAMP, QUARTER), DAY) - 1) * 60 * 60 * 24)) ;;
  }

And then I calculate the same for my rows by changing CURRENT_TIMESTAMP with ${my_date}. Then it’s easy to create a boolean field is_before_current_time_of_quarter. You just compare current_second_of_quarter with second_of_quarter built from your data

[rohit_shoppertr]

Hi Dawid,

I really appreciate your proactive approach of sharing solutions.

Here are my points:

I have created second_of_day and second_of_quarter. This gives me seconds have passed in this latest quarter which starts from 1 April.

Today is 6th april. it gave me seconds of 5 days and 6th day seconds.

Here I want to say. we should use max or latest date present in my data instead of CURRENT_TIMESTAMP(). because dimension will fail if last sales happened 6 month ago. I am trying to create a quarter over quarter chart based on latest quarter as per data present compare to last quarter as per my data.

I tried to find out latest date in my data:

 measure: period_start_max_date {
    type: date
    sql: max(timestamp_trunc(timestamp_add(${TABLE}.period_start_time,interval 30 minute), hour)) ;;
  } 

and tried to use where you have used CURRENT_TIMESTAMP() but is returning an error:

I believe it saying that I can’t call a measure in the dimension because an aggregate function is used in measure. 

Can you please help me to know, how to do all these calculation on latest date instead of CURRENT_TIMESTAMP()?

[Dawid]

Why would you need the MAX date? Even if your sale is before the current_timestamp() it’s okay because as long as it’s between “current quarter start day” and “current timestamp” it will show.

[rohit_shoppertr]

Sorry if I am missing it,

“current quarter start day” will be 1 April 2022 if I calculate it by CURRENT_TIMESTAMP().

Lets say my last sale happened on 15 March 2022, then current or latest quarter is first quarter of this year(Jan to March) and “current quarter start day” should be 1 Jan 2022.

isn’t it?

I mean if my latest sale day is 15th March then

I should compare 1 Jan to 15th March 2022/1 Jan to 15th March 2021

[Dawid]

If you want to compare first quarter of the year then you also need to develop ability to select the same quarters, in my calendar table I have “quarter_of_the_year” field. But since you wanted to compare UNTIL NOW, then this means current quarter start until now, I don’t understand why suddenly you want to move it into the past.

If you want to compare “last quarter that had a sale” then you would need to expose this MAX(date) in your data model and instead of calculating CURRENT, it would be the last quarter that had a sale but I’m not really sure why you would want that

[rohit_shoppertr]

Dawid,

I am a beginner on Looker, so please bear with me

I think I have made you confused. Let me ask again from starting:

I have a timestamp field:

  dimension_group: period_start {
    type: time
    timeframes: [
      raw,
      time,
      date,
      week,
      month,
      quarter,
      year
    ]
    datatype: datetime
    sql: ${TABLE}.period_start_time ;;
  }
 

I want to create a chart which shows latest quarter from latest year v/s same quarter from last year

for example:

Currently we are in second quarter and its 7th day so,

1 April- 7 April 2022 compare with 1 April-7 April 2021 

Lets go with  CURRENT_TIMESTAMP()

I have created second_of_day and second_of_quarter field as per your suggestion and this is the result:

Then as per your suggestion I replaced by my time field and here is the result:

Now what to do?

Did I miss anything?