How to PoP

[ryukiman07]

https://www.googlecloudcommunity.com/gc/Technical-Tips-Tricks/Methods-for-Period-Over-Period-PoP-Ana...

I implemented a POP analysis based on the above article.

The parameters to be entered are as follows.
_PoP 1. Current Date Range: 2024/04/01~2024/04/08
_PoP 2. Compare To:
Previous Period

There is only one thing to correct,
I want to be able to aggregate by the period of the date entered in the Current Date Range.

The query generated from the LookML I created is as follows.
Currently, it is designed to aggregate the period from 2024/04/01~2024/04/07.

Show More

SELECT

(FORMAT_TIMESTAMP('%Y-%m', DATE_SUB(DATE('2024-04-01'), INTERVAL ((CASE

WHEN ((( pos.receipt_date ) >= (DATE('2024-04-01')) AND ( pos.receipt_date ) < (DATE('2024-04-08'))))

THEN DATE_DIFF(DATE('2024-04-01'), pos.receipt_date, DAY) + 1

WHEN pos.receipt_date between (DATE(

DATE_ADD(DATE('2024-04-01'), INTERVAL (DATE_DIFF(DATE('2024-04-01'), DATE('2024-04-08'), DAY)) DAY)

)) and (DATE(

DATE_SUB(DATE('2024-04-01'), INTERVAL 1 DAY)

))

THEN DATE_DIFF((DATE(

DATE_ADD(DATE('2024-04-01'), INTERVAL (DATE_DIFF(DATE('2024-04-01'), DATE('2024-04-08'), DAY)) DAY)

)), pos.receipt_date, DAY) + 1

END) - 1) DAY) )) AS pos_date_in_period_month,

COALESCE(SUM(CASE WHEN (((

CASE

WHEN ((( pos.receipt_date ) >= (DATE('2024-04-01')) AND ( pos.receipt_date ) < (DATE('2024-04-08')))) THEN 'this'

WHEN pos.receipt_date between (DATE(

DATE_ADD(DATE('2024-04-01'), INTERVAL (DATE_DIFF(DATE('2024-04-01'), DATE('2024-04-08'), DAY)) DAY)

)) and (DATE(

DATE_SUB(DATE('2024-04-01'), INTERVAL 1 DAY)

)) THEN 'last' END

)) = 'this') THEN pos.excl_tax_sales_amount ELSE NULL END), 0) AS pos_current_period_sales,

COALESCE(SUM(CASE WHEN (((

CASE

WHEN ((( pos.receipt_date ) >= (DATE('2024-04-01')) AND ( pos.receipt_date ) < (DATE('2024-04-08')))) THEN 'this'

WHEN pos.receipt_date between (DATE(

DATE_ADD(DATE('2024-04-01'), INTERVAL (DATE_DIFF(DATE('2024-04-01'), DATE('2024-04-08'), DAY)) DAY)

)) and (DATE(

DATE_SUB(DATE('2024-04-01'), INTERVAL 1 DAY)

)) THEN 'last' END

)) = 'last') THEN pos.excl_tax_sales_amount ELSE NULL END), 0) AS pos_previous_period_sales,

CASE WHEN ( COALESCE(SUM(CASE WHEN (((

CASE

WHEN ((( pos.receipt_date ) >= (DATE('2024-04-01')) AND ( pos.receipt_date ) < (DATE('2024-04-08')))) THEN 'this'

WHEN pos.receipt_date between (DATE(

DATE_ADD(DATE('2024-04-01'), INTERVAL (DATE_DIFF(DATE('2024-04-01'), DATE('2024-04-08'), DAY)) DAY)

)) and (DATE(

DATE_SUB(DATE('2024-04-01'), INTERVAL 1 DAY)

)) THEN 'last' END

)) = 'this') THEN pos.excl_tax_sales_amount ELSE NULL END), 0) ) = 0

THEN NULL

ELSE (1.0 * ( COALESCE(SUM(CASE WHEN (((

CASE

WHEN ((( pos.receipt_date ) >= (DATE('2024-04-01')) AND ( pos.receipt_date ) < (DATE('2024-04-08')))) THEN 'this'

WHEN pos.receipt_date between (DATE(

DATE_ADD(DATE('2024-04-01'), INTERVAL (DATE_DIFF(DATE('2024-04-01'), DATE('2024-04-08'), DAY)) DAY)

)) and (DATE(

DATE_SUB(DATE('2024-04-01'), INTERVAL 1 DAY)

)) THEN 'last' END

)) = 'this') THEN pos.excl_tax_sales_amount ELSE NULL END), 0) ) / NULLIF(( COALESCE(SUM(CASE WHEN (((

CASE

WHEN ((( pos.receipt_date ) >= (DATE('2024-04-01')) AND ( pos.receipt_date ) < (DATE('2024-04-08')))) THEN 'this'

WHEN pos.receipt_date between (DATE(

DATE_ADD(DATE('2024-04-01'), INTERVAL (DATE_DIFF(DATE('2024-04-01'), DATE('2024-04-08'), DAY)) DAY)

)) and (DATE(

DATE_SUB(DATE('2024-04-01'), INTERVAL 1 DAY)

)) THEN 'last' END

)) = 'last') THEN pos.excl_tax_sales_amount ELSE NULL END), 0) ) ,0)) - 1 END AS pos_sales_pop_change

FROM `project_name.pos` AS pos

WHERE ((CASE

WHEN ((( pos.receipt_date ) >= (DATE('2024-04-01')) AND ( pos.receipt_date ) < (DATE('2024-04-08'))))

THEN 'This Period'

WHEN pos.receipt_date between (DATE(

DATE_ADD(DATE('2024-04-01'), INTERVAL (DATE_DIFF(DATE('2024-04-01'), DATE('2024-04-08'), DAY)) DAY)

)) and (DATE(

DATE_SUB(DATE('2024-04-01'), INTERVAL 1 DAY)

))

THEN 'Last Period'

END) IS NOT NULL )

GROUP BY

1

ORDER BY

1 DESC

LIMIT 500