Announcements
This site is in read only until July 22 as we migrate to a new platform; refer to this community post for more details.

Filter a table based on calculation of another filter

I have a report using quite basic csv data which has no true dates in it, only week numbers.

I have a table on the right showing this week information based on a drop down filter.

I would like the table on the left to be the previous weeks information.

I tried creating a calculated field called Last Week which is (max of "week" - 1) which would do the job but then I would somehow need to filter the left hand table by "week" = "Last week" but you cannot filter a number field by another field.

Any suggestions on how I could do this without having to use two filters?

image.png

0 2 672
2 REPLIES 2

Hey @mbarnes106 .
I am not sure how your fields exactly looks as you have not paste the schema, but in my opinion Looker Stuidio should automatically read the data as Week number and allow you comparisons or filtering with both tables (thhis week in one and previous week in second table)
If that is not a case with your data then you can: 

1.Try to convert it to text and then concat with current year and as a result you should be able to create a date field with ISO Year Week type which would allow you to use native comparison mode and compute the weekly dynamics.

Something like : 

CONCAT(year(today()),"W",week)

Should be a great starting point in STRING type allowing you to cast it to YEARWEEK.

Another posiblle solution is utilizing parameters based on numeric type of a field.
But I am not sure its an overkill considering two above methods should work imo.

Thank you. I will have a look at this.

At present the week field was a number field ie week 32 just had "32" in the week column.

I will have a go at converting that to a ISO Year Week field. The other issue is our businesses weeks start with wk01 being wc 30th October so ISO weeks might be a bit confusing.

Will have a look though, thank you

ps. To start with I tried using a date format but being in the UK all the dates got very confused by Google and using American formats etc