I am trying to make a one stop shop for KPIs that are found across multiple modules and are dimensionalized by a days list. I need to find these KPIs in terms of the L1D, L1W, L3W and L6W and have it update as the current day is also updated, but I'm having trouble doing it in a way in which I don't need days as a dimension first or to brute force it. I've tried using a days filter module, list properties, but was still not able to come up with a formula that correctly aggregated these metrics. I'm really trying to avoid using days as a dimension to save space. Any ideas?
As @usman.zia says, there is no dynamic time function (yet!), and it looks like your day list is a list rather than a native time dimension.
So, create a module with no dimensions (e.g.Time Lookups) that holds a line item formatted as your days list (e.g. Day Lookup). This will need to be updated as and when necessary, either manually or by some scheduled import.
In your target module, you can then use the source module.lineitem[LOOKUP:Time Lookups.Day Lookup] to pull in the results.
L1D, L1W, L3W, L6W means last 1 day, last 1 week, etc. Though since the source modules are dimensionalized by days, then it's really last 1 day, last 7 days, last 21 days, and last 42 days.
The source module is dimensionalized by a fake days list with a parent weeks list and it's updated daily to have the most current day included. I do have a module to select the last day, days in the last week, days in the last 3 weeks and 6 weeks, but haven't been able to correctly utilize it in a formula.
I'm pulling different KPIs from different source modules but all are dimensionalized by that fake days list. I can't show you that information but perhaps this could help. The picture below shows what I'm trying to avoid.