Aggregating KPIs from daily to L1D, L1W, etc

moriahreyes

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?

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

 

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.