Formula Summary type on LOOKUP or OFFSET

Hi,

We are a NZ Telecommunications company. We have the need to report metrics such as ARPU $ (avg revenue per user) and Avg cost per user across our segments. I can do this compared to our forecasts & budgets easily but if I try to do this for Prior Year or prior period data I am stuck for a solution.

I cant use formula type summation with either a LOOKUP or OFFSET which is needed to look up the prior

period comparison. I have a segment hierarchy and I need it to calculate the ARPU at each level of the list not aggregate so I need the formula to be used. I have struggled for years on this one but Prior year and prior period reporting has just jumped up in importance

Would appreciate if there was any solution out there or if this could be put forward "Request for Enhancement" to enable these important metrics to be properly calculated.

THanks

Andrew

Accepted answers

All comments

kavinkumar

HI @Andrew_Paston,

From your user case I can understand that you want to calculate the ARPU and ACPU based on historical data. And you got stuck when you try to pull the data for parent items where you are using child level list (Correct me if I am wrong). Off course you can make use of time functions like OFFSET, PREVIOUS, POST, etc.. to bring in the past data for calculations and if you don't want to do the summation for top level items then change the summary of that line item to Formula which will return you the respective value.

If you still need clarification, please do post some screen shots or files which would help us to understand your concern more!!

Thanks,

Kavin.

Andrew_Paston

Hi Kevin

thanks. My understanding is you cant use Formula type summation with a LOOKUP or OFFSET formula in a line item.

Thanks

Andrew

kavinkumar

@Andrew_Paston,

Yes, but that is for Time summary. So make your Main summary as 'Formula' and your time Summary as 'Sum' as our intention is to bring in the previous period Segment's data. Hence in this way your Segment Hierarchy will get the desired data.

I hope this helps!!

Regards,
Kavin.

Andrew_Paston

HI Kevin

Thanks, appreciate you trying to find a solution to this

I still get the error above with that Formula for main and summary for Time. However I think the solution you mentioned would end up aggregating the ARPU metrics into Time period parents....so Jan $50, Feb $50, March $50 Q1 = $150 ARPU which is not correct in the sense of a quarterly ARPU

Perhaps I am doing something silly but our Anaplan solutions architect was also stumped on this.

Thanks

Andrew

kavinkumar

Now I got your problem. For your concern, its better to use individual grids for each level (Monthly, Quarterly, Yearly).
Regards,
Kavin.

Andrew_Paston

OK thanks....I will give that a try......Appreciate your assistance

bram.kurstjens

I would also put them in seperate line items, Month_ARPU, Quarter_ARPU, Year_ARPU with summary set to None and calendar set to month, quarter, year respectively and then 1 final one on month with summary set to formula:

- IF Month_ARPU > 0 THEN Month_ARPU ELSE IF Quarter_ARPU > 0 THEN Quarter_ARPU ELSE Year_ARPU

maud_vermeulen

Hi,

I just built something that might help you, if I understand the problem well?

First build a small module for giving quarters a boolean:

These are the formulas:

Then the module with the data:

In this case taken from line items --> so created LIS and pulled all data in via COLLECT()

The formulas are set as follows: IF Offset Settings Quarters.Show Quarter? THEN Final Value - 'Final Value Q-1' ELSE IF Offset Settings Quarters.Show month? THEN Final Value - 'Final Value M-1' ELSE Final Value - 'Final Value Y-1' --> with summary FORMULA

Which results in fact tha Q in compare column show the difference of the current Q compared to teh previous Q and not just the sum of the months, same for Years.

Kind regards, Maud

Quick Links

Can you help?

Unanswered questions

import monthly data with months in columns
is there a way to import data with months in columns (usual excel table view)? I have to transform the data so there are rows to cover each period in the upload data form, but I am wondering if there is a better way to just import the data as is with the months in the columns. please let me know what you think. thank you,
Cloudworks Big Query Integration Fail
Hello, I am receiving an 'Anaplan Upload Failed' Status Description when testing my integration with a Big Query dataset. The integration imports data from BQ to our Anaplan model. No other details given in the error log. I suspect that Cloudworks is not even picking up the file but am not sure what we did wrong on the set…
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…