Filter Chart by Row Number

[DoWeAthletics]

New to Looker Studio... We have Filters (Page Level) on Page 1 with a table chart showing the top 25 results.

On Page 2, we have another table chart showing the same data; however, we want to start at row number 26 to 50.

We tried using filter - see below, but it doesn't show any data.

Help would be appreciated!

With Filter Applied:

Without Filter

[w3ir3n]

From the screenshot i would assume that you are using the [Metrics] "Record Count" that auto generated when linked from the table? 
the AUT means auto aggregation and does not work on your case
If I may suggest you can create the "Row Num" field at the data source and make sure it show 123 instead of AUT when you are trying to filter the table

Hopefully this fix your problem

[DoWeAthletics]

Thank you - Not sure if this will fix my issue, but does solve why the error is occurring.

I'm trying to find a way to show the first 20 results on Page 1, 21-40 on Page 2, 41-60 on Page 3, etc... using the page level filter.  Not sure if a formula in the database will allow this since the results are dependent on the filter.

[w3ir3n]

If you have the "Row Num" field at the data source, you can use the page filter like this:
1st page :
->Include -> Row Num -> Less than or equals to  -> 20
for 2nd and subsequence :
-> Include -> Row Num -> Greater than or equals to -> 21 -> and
-> Include -> Row Num -> Less than or equals to  -> 40

[DoWeAthletics]

Thank you w3ir3n - I've tried that, unfortunately, as I filter the data, the row numbers become obsolete as they data is not longer in sequential order. 

I'm trying to get the first 20 results from the filter on Page 1, the 2nd 20 results page 2, etc..., but it sounds like I cannot use a calculated field to do this. 

[Mehdi_Oudjida]

@DoWeAthletics

What you are trying to achieve directly inside Looker Studio is not possible (for the moment).

So you need first to add your ranking calculation as part of your original data source.
It would be a numeric dimension called "ranking".

Then, refreshing your data source (button at the bottom left corner of your field list), you will notice a new added green field (not aggregated so not blue like the record count), on which you can apply your chart or page filter (so ranking <=25 for page 1, etc).

Sorry for this additional required step of data preparation. I hope it helps.

Feel free to tell me if you need more info, otherwise you can accept this answer as a solution. 

Mehdi

[DoWeAthletics]

Thanks Mehdi - Unfortunately, this won’t work as it doesn’t take the filtering into consideration.  The Row/Ranking Column is static and doesn’t change when filtered.  I’m looking for the first 25 results on page 1, 26-50 on page 2, etc…

https://lookerstudio.google.com/reporting/b9cd5a7c-e6cb-40bd-ad10-b97d03735c30

[Mehdi_Oudjida]

@DoWeAthletics 

I suspect you missed the first step of my recommendation, adding a column in your source of data to provide the ranking to Looker Studio. What kind of data storage are you using, plugged on Looker Studio through a data source?

[DoWeAthletics]

I’m using Google Sheets and we included the additional column that identifies raw ranking.  You’ll see this as the ‘row’ column on the table, and those numbers are unaffected when filtered.

https://lookerstudio.google.com/reporting/b9cd5a7c-e6cb-40bd-ad10-b97d03735c30

[Mehdi_Oudjida]

Ok @DoWeAthletics , Try to create a calculated field "Row as Number" with the following formula

CAST(Row as NUMBER)

and apply the filter on this new numeric field.

[DoWeAthletics]

Thanks for the suggestion... it provides the same results.  

See below... I ran a filter, but it shows the static row numbers from the data set, not the top 25 results from the filter.

Sounds like I may not have a solution to this (at the moment).

[Mehdi_Oudjida]

Yes, indeed. it will evaluate and apply the filter on the value of the field "row" regarding the rule of the filter. Any post aggregation in the report based on filtering is not possible for the moment.. Sorry.

[ArkadyZagdan]

@DoWeAthletics   Considering  you have your  rank column done in the original data source,
try using filter with IN clause  (1,2...20) 

[DoWeAthletics]

Hi Arkady - Can you please elaborate?  I’m not familiar with this function.

Thank you!

[ArkadyZagdan]

Now i understand your issue. Initial post was bit misleading. It didnt state you want it to work dynamically and adjust for other dropdowns also. You can resolve  your issue with custom query.

1. connect your sheets to BQ
2. materialize it with select * on a schedule depending on your data source frequency change
3. Develop a custom query
4. connect as data source and it shhould dynamically rank your data based on filters selected
5. follow Mehdi response to set up filters