Compare total pageviews within a date range with previous date range

[sysph]

Hi there,

i'm total new to looker.  If i would like to compare total pageviews within a chosen date range let's say 2024-10-24 to 2024-10-31 with last period 2024-10-16 to 2024-10-23.  How do i achieve it with offset_list() ? thanks

[GavinW]

If you have access to the LookML code, there's a great article here showing you lots of different approaches to building different types of period-over-period analysis that allow different types of comparison. I am also led to believe that as part of the future development of LookML, users will be able to simply declare PoP and Looker will handle it for you.

If you only have front-end access, you can easily compare fixed periods (e.g. this year vs. last year, or a specific week versus another week) by filtering for the specific years, or weeks or months of the year, and pivoting on the time period. You can also do more complex things with table calculations as you suggest, but it should not be necessary if you have 'week of year' as an available timeframe in your example. 

[sysph]

Sorry. I'm pretty new to Looker.  I choose count distinct user session id. Do i pivot `week of year` ? But it's showing Column limit reached. Results may be incomplete and no data in the result section.  This view contains user, sessions, session_date, and aggregations. 

[GavinW]

There is a maximum of 200 columns available in Looker's front end, which is why you see that message - you must have 4+ years of data available in the database.

You could get 2 years of data, week by week, in 104 columns if you apply a filter on 'session date', or even a filter on 'session year'.

Maybe you don't want to see the data week by week? In which case you could pivot instead on session month, or quarter, or year. 

[sysph]

Hi Gavin, 

Thanks for the reply.  Yup, i got lots day of data in that table.  I just want to see the disparity of current period (2024-10-24~2024-10-31  or Last 14 days ) and last period (2024-10-16~2024-10-23 or previous last 14 days).  

What should i do?  thanks

[sysph]

I can try session month first and see what i got. 

[GavinW]

I think it's really difficult to do this from the front end for irregular time periods (e.g. arbitrary start and end dates, two-week periods etc). If you want to compare periods that come in the time dimension groups (weeks, months, days, quarters etc.) it is very straightforward with pivots and filters.

In better news, there is new functionality coming to the semantic layer next year that will allow the data team to easily set up dimensions for PoP analyses with just a couple of lines of code.

[sysph]

Hi Gavin,

Thanks for the getting back to me.  yup, it is difficult at this point.  I can only implement the PoP analyses.

That's great news!