https://www.gooddata.ai/ logo
Join Slack
Powered by
Hi GoodData team, May I confirm if it is possible ...
# gooddata-cloud
m
Hi GoodData team, May I confirm if it is possible to implement a dynamic grouping of barristers based on the selected date (date filter)? For context, this is the requirement:
Copy code
We need to create a metrics that dynamically groups barristers based on the number of years since their Bar Roll date and Date of Silk date. Group membership should update automatically when a date range filter is applied on the dashboard — i.e. seniority is calculated relative to the selected date, not today's date.

The purpose of this is to display, for any given time period, a list of barrister names within each group along with their associated activity data (total tasks, case numbers, etc.).

Grouping logic:

Group 1 : Senior silks (12+ years in silk) & Mid level silks (5 to 11 years in silk)
Barristers with a Date of Silk that was 5 or more years before the selected date

Group 2 : Junior silks (5 years and below in silk)
Barristers with a Date of Silk that was less than 5 years before the selected date.

Group 3 : Senior juniors (18+ years call) & Juniors (12-17 years call)
Barristers with no Date of Silk whose Bar Roll date was 12 or more years before the selected date

Group 4 : Middle juniors (6-11 years call)
Barristers with no Date of Silk whose Bar Roll date was between 6 and 11 years before the selected date.

Group 5 : Junior juniors (0-5 years call)
Barristers with no Date of Silk whose Bar Roll date was less than 6 years before the selected date.

Relevant date fields on the barrister profile:
- dt_call
- dt_silk
To apply, I opted to create a new dataset for the groupings, since we cannot convert metrics into attributes. However, I'm not sure if this change returns the desired result. Could you please help check if this is feasible? Thank you so much in advance 🙌
k
Dynamic Barrister Grouping Based on Selected Date Filter This is a well-structured use case, but unfortunately it runs into a known platform limitation in GoodData. Core Limitation: MAQL Cannot Access Dashboard Filter Boundaries The fundamental blocker is that MAQL metrics cannot read the selected date from a dashboard date filter as a variable. You cannot dynamically compute "years since dt_silk/dt_call relative to the filter's selected date" and use that as a parameter inside a metric or grouping definition. [Filter boundaries] This means the anchor point for your seniority calculation (
selected_date - dt_silk
or 
selected_date - dt_call
) cannot be derived dynamically from the dashboard filter in MAQL. This is a documented product limitation that has been raised by multiple community members. [Overlap dates] Additionally, GoodData does not support 
>=
/ 
<=
operators on date filters, which would otherwise be needed to express conditions like "dt_silk was 5+ years before the selected date" dynamically. [No / < filters>] Regarding Your New Dataset Approach Creating a new dataset for groupings is a step in the right direction, but the dynamic aspect remains the challenge. A static dataset would only reflect groupings as of a fixed date (e.g., today), not relative to whatever date the user selects on the dashboard. Practical Workarounds 1. Precompute groupings at the data source level (most robust): Add a snapshot/period table in your database that, for each barrister and each relevant time period (e.g., month or year), pre-calculates which seniority group they belong to. GoodData can then simply filter and display that pre-flagged group attribute. This sidesteps the MAQL limitation entirely. [Precompute at source] 2. Hardcode the groupings for a fixed reference date: Define metrics with static 
WHERE
clauses for a fixed date. This is not dynamic and requires manual updates when the reference date changes, but is technically feasible in MAQL. [WHERE clause] 3. Submit as product feedback: The ability to reference dashboard filter boundaries (e.g., 
filter.selected_date
) as variables inside MAQL has been explicitly requested by other community members and GoodData support has been logging these as product feedback items. [Product feedback] Recommendation The most scalable solution given current platform capabilities is to precompute the seniority group in your data warehouse — for example, a snapshot table with columns like 
barrister_id
, 
period
, 
seniority_group
— and then build your visualizations in GoodData using that pre-calculated attribute. This would allow the dashboard date filter to scope the population to the correct period's groupings naturally. If a fully native dynamic solution is critical, I would recommend reaching out to GoodData support to formally log this as product feedback. 👍 0 button 👎 0 button ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups. 👀 Our Support team monitors all messages and will jump in if needed.
m
MySQL view > 
vw_user
definition:
Copy code
SELECT
        `user`.`id` AS `user_id`,
        CONCAT(`user`.`first_name`,
                ' ',
                `user`.`last_name`) AS `user_name`,
        `user`.`id_user_type` AS `user_type_id`,
        `user`.`dt_created_utc` AS `user_created_date`,
        IF((`user`.`is_active` = 1),
            'true',
            'false') AS `user_is_active`,
        `user`.`email` AS `user_email`,
        `user`.`gender` AS `user_gender`,
        `user`.`mobile` AS `user_mobile`,
        `user`.`title` AS `user_title`,
        `preference`.`insurance_limitation_value` AS `insurance_limitation_value`,
        `preference`.`annual_invoice_target` AS `annual_invoice_target`,
        `preference`.`annual_receipt_target` AS `annual_receipt_target`,
        COALESCE(`preference`.`id_cps_area`, 0) AS `user_cps_area_id`,
        `preference`.`dt_inn_call` AS `user_date_of_call`,
        `preference`.`dt_silk` AS `user_date_of_silk`,
        (CASE
            WHEN
                (TIMESTAMPDIFF(YEAR,
                    `preference`.`dt_silk`,
                    `user`.`dt_created_utc`) >= 5)
            THEN
                1
            WHEN
                (TIMESTAMPDIFF(YEAR,
                    `preference`.`dt_silk`,
                    `user`.`dt_created_utc`) < 5)
            THEN
                2
            WHEN
                ((`preference`.`dt_silk` IS NULL)
                    AND (TIMESTAMPDIFF(YEAR,
                    `preference`.`dt_inn_call`,
                    `user`.`dt_created_utc`) >= 12))
            THEN
                3
            WHEN
                ((`preference`.`dt_silk` IS NULL)
                    AND (TIMESTAMPDIFF(YEAR,
                    `preference`.`dt_inn_call`,
                    `user`.`dt_created_utc`) BETWEEN 6 AND 11))
            THEN
                4
            WHEN
                ((`preference`.`dt_silk` IS NULL)
                    AND (TIMESTAMPDIFF(YEAR,
                    `preference`.`dt_inn_call`,
                    `user`.`dt_created_utc`) < 6))
            THEN
                5
            ELSE 0
        END) AS `barrister_seniority_group_id`
    FROM
        (`users` `user`
        JOIN `users_preferences` `preference` ON ((`preference`.`id_user` = `user`.`id`)))
    WHERE
        (`user`.`is_deleted` = FALSE)
I used the user's created date for now
j
Hi Mira, would you also be able to send over a copy of your LDM json file? You can send it to me in a DM if your prefer
✅ 1
m
Hi Joseph, I've sent you the copy via DM 🙂
j
this is fully achievable, it just needs a change to how the data is structured before it reaches GoodData. No new platform features needed.
⚠️ Please test this in a test/staging workspace first before applying to production. The LDM change removes an existing relationship on the 
users
dataset, which could affect any visualisations currently using 
barrister_seniority_group_name
or 
barrister_seniority_group_id
as attributes.
The core issue with the current view Your 
vw_barrister_seniority_group
view gives every barrister one fixed group, calculated against a static date. When a user applies a year filter on the dashboard, there's nothing to update — the group never changes. Step 1 — Update your MySQL view Replace 
vw_barrister_seniority_group
to produce one row per barrister × year, with a single surrogate key as the grain (GoodData only supports single-attribute grains) and the seniority group calculated relative to the end of each year: sql
Copy code
SELECT
    CONCAT(u.id, '_', p.period_year)  AS user_seniority_period_id,
    u.id                              AS user_id,
    p.period_year                     AS period_year,
    p.period_end_date                 AS period_end_date,
    CASE
        WHEN pref.dt_silk IS NOT NULL
             AND TIMESTAMPDIFF(YEAR, pref.dt_silk, p.period_end_date) >= 5  THEN 1
        WHEN pref.dt_silk IS NOT NULL
             AND TIMESTAMPDIFF(YEAR, pref.dt_silk, p.period_end_date) < 5   THEN 2
        WHEN pref.dt_silk IS NULL
             AND TIMESTAMPDIFF(YEAR, pref.dt_inn_call, p.period_end_date) >= 12 THEN 3
        WHEN pref.dt_silk IS NULL
             AND TIMESTAMPDIFF(YEAR, pref.dt_inn_call, p.period_end_date) BETWEEN 6 AND 11 THEN 4
        WHEN pref.dt_silk IS NULL
             AND TIMESTAMPDIFF(YEAR, pref.dt_inn_call, p.period_end_date) < 6 THEN 5
        ELSE 0
    END AS barrister_seniority_group_id,
    CASE
        WHEN pref.dt_silk IS NOT NULL
             AND TIMESTAMPDIFF(YEAR, pref.dt_silk, p.period_end_date) >= 5  THEN 'Silks (5+ years)'
        WHEN pref.dt_silk IS NOT NULL
             AND TIMESTAMPDIFF(YEAR, pref.dt_silk, p.period_end_date) < 5   THEN 'Junior Silks (under 5 years)'
        WHEN pref.dt_silk IS NULL
             AND TIMESTAMPDIFF(YEAR, pref.dt_inn_call, p.period_end_date) >= 12 THEN 'Senior Juniors (12+ years call)'
        WHEN pref.dt_silk IS NULL
             AND TIMESTAMPDIFF(YEAR, pref.dt_inn_call, p.period_end_date) BETWEEN 6 AND 11 THEN 'Middle Juniors (6-11 years call)'
        WHEN pref.dt_silk IS NULL
             AND TIMESTAMPDIFF(YEAR, pref.dt_inn_call, p.period_end_date) < 6 THEN 'Junior Juniors (0-5 years call)'
        ELSE 'Unclassified'
    END AS barrister_seniority_group_name
FROM users u
JOIN users_preferences pref ON pref.id_user = u.id
CROSS JOIN (
    SELECT 2015 AS period_year, '2015-12-31' AS period_end_date UNION ALL
    SELECT 2016, '2016-12-31' UNION ALL
    SELECT 2017, '2017-12-31' UNION ALL
    SELECT 2018, '2018-12-31' UNION ALL
    SELECT 2019, '2019-12-31' UNION ALL
    SELECT 2020, '2020-12-31' UNION ALL
    SELECT 2021, '2021-12-31' UNION ALL
    SELECT 2022, '2022-12-31' UNION ALL
    SELECT 2023, '2023-12-31' UNION ALL
    SELECT 2024, '2024-12-31' UNION ALL
    SELECT 2025, '2025-12-31'
) p
WHERE u.is_deleted = FALSE
Step 2 — Three LDM changes in GoodData a) Update 
barrister_seniority_groups
dataset (Create LDM Manually): BeforeAfterGrain`barrister_seniority_group_iduser_seniority_period_id`New attributes—`user_seniority_period_id`, `seniority_period_year`New FK—`user_id` → `users.user_id`New date reference—`period_end_date` → 
seniority_period_date
dimension b) Remove the FK from 
users
→ 
barrister_seniority_groups
 — the old static one-group-per-barrister link on the 
users
dataset needs to be deleted. c) Add a new 
seniority_period_date
date instance — same shared date dimension already used for 
task_date
, 
matter_date
etc. See: Using Date Dimensions How it works after the change
Copy code
tasks ──(task_date)──▶ date dimension ◀──(period_end_date)── barrister_seniority_groups
  │                                                                    │
  └──(user_id)──▶ users ◀──────────────────(user_id)─────────────────┘
When a user selects "2022" on the dashboard, the date filter automatically scopes 
barrister_seniority_groups
to the 2022 rows — which already have the correct group pre-calculated as-at end of 2022. 
barrister_seniority_group_name
then behaves like any regular attribute. No MAQL required. For full reference: Dataset concepts · Prepare your data / naming conventions
👀 1
m
Thank you for this, @Joseph Heun 🙌 Could you please help check this additional response (⬇️) sent by our team and see if it is feasible and aligns with the suggested 
vw_barrister_seniority_group
changes? > So basically when we set a date range on the main filter on the dashboard for example lets say the last 6 months of last year. Based on this date range, the groups then need to change depending on that dates distance from their year of call or year of silk, depending on the group if its a silk group or not. Does that make sense?
j
Yes, that makes sense — and it's an important detail to flag. The approach we outlined will work for most date ranges, but there is one gap: the view produces one row per barrister per year, anchored to December 31st. This means if a user selects a date range that doesn't include a December 31st — for example, January to June 2024, or a single month like June — no seniority rows would match and the dashboard would show nothing. The fix is straightforward: generate one row per barrister per month instead of per year, anchored to the last day of each month. That way, any date range a user picks on the dashboard will always contain at least one anchor date, and the correct seniority group for that period will show up reliably. There is one thing worth clarifying with your team before building this: when a user selects a date range, which point in that range should the seniority grouping be calculated from? For example, if they select July–December 2024, should the groups reflect where each barrister stood at the end of that period (December), or the start (July)? In most cases the end of the range is the right answer, but it's worth confirming since a barrister could technically move between groups mid-range if their anniversary falls within the selected dates. Please test any changes in a staging environment before applying to production.
m
Let me confirm with them and get back to you 🙏 Thanks
Hi @Joseph Heun, I've confirmed that this is calculated at the end of the period, and is expected to be filtered by Month instead of Year
j
The view needs to be updated to produce one row per barrister per month, with the seniority group calculated relative to the last day of that month. When a user applies a date range filter on the dashboard, GoodData will automatically use the rows matching the end of the selected period, which means the seniority groups will always reflect where each barrister stood at the close of that month. The LDM changes remain the same as previously described — the only difference from the earlier recommendation is that your 
period_end_date
column should anchor to the last day of each month rather than each year, and the date dimension on 
barrister_seniority_groups
should be set to month granularity in GoodData to match how users will filter on the dashboard.
m
Thanks @Joseph Heun Here is the updated view (note: date range is from 2015-2026)
Copy code
WITH RECURSIVE months AS (
    SELECT CAST('2015-01-01' AS DATE) AS m_start
    UNION ALL
    SELECT m_start + INTERVAL 1 MONTH
    FROM months
    WHERE m_start < '2026-12-01'
),
p AS (
    SELECT
        m_start AS period_date,
        LAST_DAY(m_start) AS period_end_date
    FROM months
)
SELECT
    CONCAT(`u`.`id`, '_', DATE_FORMAT(`p`.`period_date`, '%Y%m')) AS `user_seniority_period_id`,
    `u`.`id` AS `user_id`,
    YEAR(`p`.`period_date`) AS `period_year`,
    MONTH(`p`.`period_date`) AS `period_month`,
    `p`.`period_end_date` AS `period_end_date`,

    (CASE
        WHEN (`pref`.`dt_silk` IS NOT NULL AND TIMESTAMPDIFF(YEAR, `pref`.`dt_silk`, `p`.`period_end_date`) >= 5) THEN 1
        WHEN (`pref`.`dt_silk` IS NOT NULL AND TIMESTAMPDIFF(YEAR, `pref`.`dt_silk`, `p`.`period_end_date`) < 5) THEN 2
        WHEN (`pref`.`dt_silk` IS NULL AND TIMESTAMPDIFF(YEAR, `pref`.`dt_inn_call`, `p`.`period_end_date`) >= 12) THEN 3
        WHEN (`pref`.`dt_silk` IS NULL AND TIMESTAMPDIFF(YEAR, `pref`.`dt_inn_call`, `p`.`period_end_date`) BETWEEN 6 AND 11) THEN 4
        WHEN (`pref`.`dt_silk` IS NULL AND TIMESTAMPDIFF(YEAR, `pref`.`dt_inn_call`, `p`.`period_end_date`) < 6) THEN 5
        ELSE 0
    END) AS `barrister_seniority_group_id`,

    (CASE
        WHEN (`pref`.`dt_silk` IS NOT NULL AND TIMESTAMPDIFF(YEAR, `pref`.`dt_silk`, `p`.`period_end_date`) >= 5) THEN 'Silks (5+ years)'
        WHEN (`pref`.`dt_silk` IS NOT NULL AND TIMESTAMPDIFF(YEAR, `pref`.`dt_silk`, `p`.`period_end_date`) < 5) THEN 'Junior Silks (under 5 years)'
        WHEN (`pref`.`dt_silk` IS NULL AND TIMESTAMPDIFF(YEAR, `pref`.`dt_inn_call`, `p`.`period_end_date`) >= 12) THEN 'Senior Juniors (12+ years call)'
        WHEN (`pref`.`dt_silk` IS NULL AND TIMESTAMPDIFF(YEAR, `pref`.`dt_inn_call`, `p`.`period_end_date`) BETWEEN 6 AND 11) THEN 'Middle Juniors (6-11 years call)'
        WHEN (`pref`.`dt_silk` IS NULL AND TIMESTAMPDIFF(YEAR, `pref`.`dt_inn_call`, `p`.`period_end_date`) < 6) THEN 'Junior Juniors (0-5 years call)'
        ELSE 'Unclassified'
    END) AS `barrister_seniority_group_name`
FROM `users` `u`
JOIN `users_preferences` `pref` ON `pref`.`id_user` = `u`.`id`
CROSS JOIN `p`
WHERE `u`.`is_deleted` = FALSE
s
Hi Mira, The view looks good on our end. Have you had a chance to apply the LDM changes that my colleague Joseph outlined? Please give it a try and let us know the result.
m
Hi @Shamil Akhmadyshev My apologies for the delayed response. Yes, I was able to apply the LDM changes, and the groupings work as expected. However, I think with this change, I realized that we may not be able to add the m matter/task-related metrics/attributes/facts here 🙏 I believe this is due to the connection's direction in the LDM, but as per requirement, we would need to show a list of barrister names within each group, along with their associated data such as total tasks, case numbers, etc... Is this possible?
m
Hi Mira, from the LDM screenshot you shared, we can already see that 
Barrister Seniority Groups
only connects with 
Users
and 
Date
— there's no link to your Tasks/Matters dataset, which is why those metrics are showing as unrelated. That said, we'd like to see the full LDM to confirm the complete picture and suggest the right fix. Could you let us know which GoodData domain/org you're working in?
m
Hi @Mauricio Cabezas, here is our Staging env: https://barbooks-au-test.cloud.gooddata.com
j
One thing still outstanding — the LDM in GoodData still needs to be updated as described in the previous message. The view is ready, but until 
user_seniority
has a proper grain (
user_seniority_period_id
), its 
period_end_date
column is connected to a date dimension, and `barrister_seniority_group_id`/`barrister_seniority_group_name` are added as attributes, the dashboard filter won't be able to use it. Please test in staging first before applying to production. If you want, I can try and provide an update view of the json, but please test in a new workspace to ensure it fits your use case before applying to any other used workspace.
m
Hi @Joseph Heun, thank you for pointing that out. I'm not sure I follow the remaining item to implement, apologies for the confusion. Is it alright if I could get a copy of the updated view so that I can review it?
j
m
Hi @Joseph Heun, thank you so much for this one 🙏 Please see my updated view below inline with the remaining changes. Given this, it seems that the other required matter/task-related metrics are still not available when creating an insight. Also, I just wanted to note that Seniority and Barrister Seniority Group (custom based on a specific grouping logic) are different
Master Workspace.json
j
Hi Mira, thank you for sharing the updated file. I've reviewed it and prepared a corrected LDM JSON for you to deploy. The only change made was to the 
user_seniority
dataset, which was not updated in your version. It has now been given the correct grain (
user_seniority_period_id
), all required attributes (
barrister_seniority_group_id
, 
barrister_seniority_group_name
, 
period_year
, 
period_month
), a foreign key reference to 
users
, and a connection to a new 
seniority_period_date
date dimension — all mapped to your existing 
vw_user_level_expertise
view in the 
demo_uk
schema. Everything else in your LDM has been left exactly as-is. please note that when building your insight, the date filter will need to be applied to Seniority Period Date to scope the seniority groups correctly to the selected period. The 
tasks
and 
matter
metrics connect through 
users
and will be available as normal. Please test in a staging environment before applying to production, and let us know if you have any questions.
Test LDM for new connections.rtf
m
Thank you @Joseph Heun! I appreciate your response
6 Views
Open in Slack
PreviousNext
Powered by