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

[Kevin_F_McCarth]

Hello ryukiman07,

If I understand correctly, your issue is with the end date: the logic is not including the end date entered by the user.  I would note that this is the standard way that date ranges are used in looker filters: the end date is typically excluded.

That said, you can try to work around this (if you can be sure your users will expect this to be inclusive of the end date).
If you based your code of the linked article, you will find multiple uses of {% date_end [filter/field_name] %}.  You would need to adjust those references by adding one day to them presumably with date_add sql.  

Elsewhere, you will see {% condition current_date_range %}.  In that case, you will need to convert to managing the range with date_start and date_end (similar to what is done for period_2), because as mentioned above: looker standard behavior (what condition block will do) will not include the end date of a date range.

I hope this helps conceptually - as you can see you will need to apply updates in multiple places to adjust how this range is handled - you will need to carefully adjust all those checks that happen against the end date of the user date selection.  And be sure to check closely that the days_in_period calculation and other helper calculations are all working correctly.

[salehaxsid]

You can try this approach too for Periodic Analysis
Solved: How to implement DatesYTD and DatesMTD using Look... - Google Cloud Community