0

Feature request: A simpler way to align a data series against a list of dates

A new Klipfolio user here. I've been very helpfully given advice on how to ensure that if a series has gaps in the data, we are still able to return "0" instead of skipping that date's value. Given how common this use case is, it would be awesome to simplify how to align data with dates without resorting to nesting functions in the LOOKUP function. To that note, I would LOVE it if you could create an extra optional parameter to simplify the formula and make it more readable:

GROUPBY ( GROUPBY(values, measure, [method], [align]). This new optional parameter would take what is in values and aligns it against what is in [align], filling in gaps with 0 and dropping any that don't match up.

An example of this in use: say you wanted to graph the number of users from a country by date across an x-axis, this would be the new formula:

GROUPBY ( SELECT ( date, country=GB ),
                     SELECT ( users, country=GB ), "sum", !x-axis )

This is to replace needing to use a long nested function:

LOOKUP ( !x-axis,
                       GROUP ( SELECT( dates, country="GB"),
                       GROUPBY( SELECT( dates, country="GB"),
                                           SELECT( users, country="GB") , "sum")

For Excel users, this is a bit like the IFERROR function, which was introduced later and removed the need to have a nested IF(ISERROR(...)) :)

PS: Btw, I just thought of the added benefit that if the !x-axis is running off some sort of date picker, then you don't even need to add the date picking formulae to the data series as those unmatched dates would be automatically dropped!

1 comment

  • 0
    Avatar
    Stephen Yates

    Hi, thank you for the feedback. I agree that simplifying how to align data with dates would benefit a lot of users. I will share your comments with the product team.

Please sign in to leave a comment.