Aggregating KPIs from daily to L1D, L1W, etc

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?

Accepted answers

ArunManickam

Hello @moriahreyes ,

See If you could use this approach,

Lists

Blue print view,

Line item formulas,

Score
Date	Fake Day List.Date
Day Aggregate	IF Today - Date + 1 <= Day Aggregate.Days from today THEN ITEM(Day Aggregate) ELSE BLANK
~Day Aggregate	Day Aggregate

Note : Day Aggregate line item must have summary as "First Non Blank"

Created your module by Day aggregate dimension as follows,

Key is to calculate the day aggregate list for each day, as follows. Note the summary.

L1D must be cumulated inside L3D, L5D, etc

L3D must be cumulated inside L5D, L10D

Thanks

Arun

All comments

usman.zia

Hi @moriahreyes,

Thanks for posting your question, its very interesting to see your issue!

Please could you define what the follow items mean:

L1D

L1W

L3W

L6W

Just that I have not seen these before so it would help to gain a better understanding and I'm sure for other members too.

Currently it is not possible to have a formula that defines the current day or week.

So for this you will need to create a module where you have a line item where a user or you can manually set the current day but this will need to be regularly updated.

Currently there is a need for a current day formula and there is an idea you can support in the link below:

https://community.anaplan.com/t5/Idea-Exchange/Current-Date-and-Time/idi-p/46550

In the module that you are using to source the KPIs that are at day scale are they dimensioned at real day scale or a fake day dimension which is then also dimensioned by week?

It is definitely possible to aggregate the days into weeks if you have the right summary method in place.

Maybe if you can share your pivot view of the source module in question with a bit of detail of what you want the KPIs to achieve I'm sure the community or me can solve this!

Thanks,

Usman

DavidSmith

@moriahreyes

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.

I hope that helps

David

moriahreyes

How would I go about making a line item time look up for L1W, L3W and L6W (meaning I'd have to find and aggregate the kpi for the last 7 days, last 21 days, and the last 42 days)?

moriahreyes

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.

ArunManickam

Hello @moriahreyes ,

See If you could use this approach,

Lists

Blue print view,

Line item formulas,

Score
Date	Fake Day List.Date
Day Aggregate	IF Today - Date + 1 <= Day Aggregate.Days from today THEN ITEM(Day Aggregate) ELSE BLANK
~Day Aggregate	Day Aggregate

Note : Day Aggregate line item must have summary as "First Non Blank"

Created your module by Day aggregate dimension as follows,

Key is to calculate the day aggregate list for each day, as follows. Note the summary.

L1D must be cumulated inside L3D, L5D, etc

L3D must be cumulated inside L5D, L10D

Thanks

Arun

Quick Links

Can you help?

Unanswered questions

How have you from-scratch built lot-level, geo-based Inventory Consumption?
We’re working on modeling inventory consumption at the lot level, accounting for shelf life considerations, since we need visibility into which lots may not consume—both for risk reporting and E&O analysis. Each item may have customers with different shelf life requirements. For example, item A may need 270 days for some…
How to bring Row data into Columns?
Hi, Can you help me, how can we get row data into columns using Unique ID? Per attached below first snapshot, I want to bring rountingOrder 4, RecipientName, Sent and Completed dates in 1 row as show in sanpshot 2. Do I need to create Hierarchy list and Map?
How to Design Simulation Planning in Anaplan with Multi-Level User Input and Step-by-Step Logging?
Hi Anaplan Community, I'm working on a simulation planning model where users need the flexibility to input pricing adjustments at any level of the product hierarchy, and I’d love your guidance on best practices. 📌 Scenario Overview: We have a 4-level product hierarchy: Brand Category Sub Category SKU (lowest level) The…