Hi there, I’m hoping someone can help me with my problem concering using Arrays from a Bigquery table in combination with dropdown filters in Looker Studio. Basically my case is like this. I have a table with employees for each (school) year. One row per employee/school year combination. Underneath this is an array with 1 row per unique location that this employee works at. Underneath this array is another array with the unique absence registrations per location that this person works at. Below is an example of the table:
| Schooljaar | Medewerker_ID | Formatiedetails.Werkgever_ID | Formatiedetails.Volgnummer_Dienstverband | Formatiedetails.Kostenplaats_Code | Formatiedetails.Kostenplaats | Formatiedetails.Kostendrager_Code | Formatiedetails.Kostendrager | Formatiedetails.Organisatorische_Eenheid_Code | Formatiedetails.Organisatorische_Eenheid | Formatiedetails.Functie | Formatiedetails.Type_Contract | Formatiedetails.Hoofddienstverband | Formatiedetails.Vervangingsdienstverband | Formatiedetails.Uitbreidingsdienstverband | Formatiedetails.Loonschaal | Formatiedetails.Trede | Formatiedetails.Aantal_Unieke_Verzuimen | Formatiedetails.Verzuim.Verzuimmelding_ID | Formatiedetails.Verzuim.Type_Verzuim |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 2024/2025 | 10013678 | XXX | 1 | 62 | bestuursbureau | 62 | HSL Bestuursbureau | 906 | Medewerker Personeel en Organisatie | Role H | Onbepaalde tijd | TRUE | FALSE | FALSE | Schaal 10 | 12 | 3 | 9745 | Ziek als gevolg van zwangerschap |
| 9638 | Zwangerschap / bevalling |
||||||||||||||||||
| 9244 | Ziek | ||||||||||||||||||
| CENTRAAL personeel | 67 | HSL Verlof | 906 | Medewerker Personeel en Organisatie | Role H | Onbepaalde tijd | TRUE | FALSE | FALSE | Schaal 10 | 12 | 3 | 9745 | Ziek als gevolg van zwangerschap |
|||||
| 9638 | Zwangerschap / bevalling |
||||||||||||||||||
| 9244 | Ziek | ||||||||||||||||||
| Zuid | 61 | Zuid | 906 | Medewerker Personeel en Organisatie | Role H | Onbepaalde tijd | TRUE | FALSE | FALSE | Schaal 10 | 12 | 2 | 9745 | Ziek als gevolg van zwangerschap |
|||||
| 9244 | Ziek |
What now becomes an issue for me is when trying to use filter dropdowns in Looker Studio. When I do this, the table is flattened, thus rows are duplicated. I would like to make a calculation using the FTE_Gewerkt column and the DISTINCT count Verzuimmelding_ID column. I want to calculate the frequency of distinct verzuimmelding IDs divided by the sum of FTE_Gewerkt. What I would like to happen is that whenever I filter out a specific kostendrager that the FTE_Gewerkt for that record is not counted towards the sum. Whenever I filter out a verzuimmelding ID however, I would like Looker Studio to filter out only that record in that specific array. This is however not the case, as the initial table is flattened. Therefore my FTE_Gewerkt value is in the table 8 times, for each Verzuimmelding_ID record. For the Verzuimmelding_IDs I can easily handle this by simply using COUNT_DISTINCT(Verzuimmelding_ID), but for the sum of FTE_Gewerkt I can’t do this trick of only using the distinct values. Furthermore when I filter out the Verzuimmelding_IDs 9745 and 9244 then this entire FTE_Gewerkt row is deleted from the sum (which I don’t want).
How can you best handle cases like this when working with nested arrays in Looker Studio?