Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Solved

Dynamic Period-over-Period Filter Comparison

  • 24 November 2022
  • 1 reply
  • 252 views
L

Hi,

Sorry if my English is not the best.

I’m working with a client who needs to realize a dynamic comparissons on different dates.

The point is that, the people who see the dashboard, must have the possibility of select a certain date and the dashboard should generate the next comparissons:

  • Selected Month vs Previous Month
  • Selected Month vs Same Month on Last Year
  • Selected Month vs December Previous Year

We’re trying to do this functionality using Liquid and parameters, but we can’t reach to the desired result.

For now, we managed to realize the comparison between the MAX month on the database, bus it is not dynamic.

 

Thanks in advance!

icon

Best answer by olga 27 November 2022, 16:43

View original
This topic has been closed for comments

1 reply

olga
Rank
Userlevel 2

Hi, there are multiple ways to do the PoP analysis but most of them are pretty complex, and they also include the “period” definition as user can select week, month, year, custom period range

 In your case it seems like you could simplify them. 

Do I understand correctly that users can select a date on the calendar? 

So then, from this date which will be a parameter you can extract the value for month and for year, and then apply some of the methods described or simplify things with sortings and table calcs. 

 

For example, for the “Selected Month vs Previous Month”  you can have a month as a dimension, sort on the dimension and visualize first 2 rows. 

For the “Selected Month vs Same Month on Last Year” you can sort on the year, so you would have this year and last year, and filter on the month (like month number = 2 or month name = “February”) 

A bit more advanced thing will be “Selected Month vs December Previous Year” where you can first extract the values for the year and for the month from the user selection, and then you need only 2 combinations “month selected + year selected” and “December + (year selected - 1)” . You can have these calculations in the custom filter of the tile, in LookML, or directly in your explore as table calculations, like additional a dimension for real month-year, a table calc for ““month selected + year selected” and a table calc for “December + (year selected - 1)”. When you have this, you can compare if your dimension is equal to any of these 2 table calcs, which will be a yesno value → and you can hide all the No’s from visualization, which will leave you with the 2 rows you need

Terms of Service