Infoflex help

­­General Discussions
1

I’m having some problems with the Infoflex process and I’m hoping there’s some friendly experts here who could assist.
I have a Sales Cube which has Sales Orderbook values per customer split into different Product Groups. The cube has a version dimension which has ACT (Actual) and BDGT (Budget) members.

The Product Group dimension has members which match our revenue codes on the nominal ledger.

I want to be able to spread my budget figures for the next financial year based upon the spread of the corresponding customer in the corresponding month of the previous year. So, looking at month 1 in last financial year, if Customer X had sales values of 25% in Product Group 4001 and 75% in Product Group 4002, I want to spread the BDGT figures for this financial year according to those percentages for that specific month.

The problem I’m having is where a customer has no sales in a particular month last year but there is a budget for that month in the next financial year - so I get a divide-by-zero error.

What I really want it to do is to calculate the split by Product Group for the entire year (last financial year) and to apply that split to each month in the budget year.

And…if that lot makes sense, and you’re still reading, you’re doing well!

Any suggestions?

Thanks…

2

Hi Alan, @alan.smithers

Hope you’re well. Looks like you want to create the split based on the aggregated prior year total, and not the value in the individual months of the prior year? If that’s the case, then I would recommend the below:

  1. Create a stat version (say STAT)
  2. Run an infoflex to copy the data (having time in the source/target) from your (say) ACT version (say) 2020 leaf descendants aggregated to this STAT version’s 2021 months.
    This will create a value in each of 2021 months that is = the total of 2020.
  3. For the spread values to occur, create an Infoflex to spread based on existing data, where in the variables you would select this STAT version. This will use spread based on the year totals in the STAT version. Note: if you still have no customer sales values for the year, you will still face the divide-by-zero error. If that’s the case, then other steps will need to be incurred for such scenarios.

Hope this helps,
Navin

3

Hi Navin,

Thanks for getting back to me.
Yes, that’s the approach I was going to try next but I was wondering if I was missing a better solution.
Good to know I’m not perhaps quite as green as I’d feared!
I’ll give that a go and let you know how I get on.

Alan

2 Likes
4

@alan.smithers, I’d consider using a template calculation rather than an infoflex. In a template you could:

  1. Use the customer 2020 sum member as the basis of your calculation
  2. Use excel like formulae to account for div/0 however you like
  3. Make use of spreads if you like, similar to infoflex.

In a “template calculation” process, you are making use of a template you’ve written for the purpose of calculating complex results and storing it in the cube, without a human having to open the template and save it.

Depending upon how complex you need to get with your treatment of exceptions, sometimes template calculations are not only more capable but more intuitive for excel experts.