Split year results into months results

[Former Member]

Hello,

In SAP Lumira, there are 2 tables, which are common for sales topics :

Table A: Actual sales

Product     Month          Total month

PCs             1/2016            52300

PCs             2/2016            26300

PCs             3/2016            92100

Printers        1/2016            36000

Printers        2/2016            63000

Printers        3/2016            12000

Table B: Forecast sales

Product     Forecast 2016

PCs               600 000

Printers          300 000

The expected result is a table comparing actual results with forecasted results by month:

Result:

Product     Month          Total month     Forecast

PCs             1/2016            52300          50000

PCs             2/2016            26300          50000

PCs             3/2016            92100          50000

Printers        1/2016            36000          25000

Printers        2/2016            63000          25000

Printers        3/2016            12000          25000

The aim is to be able to see the gaps between both values.

I know it is possible to do that with a separated excel file, but is there a more simple way to do it directly in Lumira?

Many thanks in advance,

Nicolas

[former_member198672 · Accepted solution]

Hi Nicolas,

If you don't want to use 2 excels, then I feel your dataset should have the "Forecast 2016" value duplicated for each month. I created a dummy dataset of your data and it looks like this

Then I created a calculated measure called Monthly Forecast using the formula {Forecast 2016} / 12

Then, I changed the aggregations of Forecast 2016 and Total month to "None"

I was able to get the table you are asking for like this

But, since you say "The aim is to be able to see the gaps between both values", I would suggest these visualizations that gives a clear understanding of the gaps

Is this what you're looking for?

Regards,

Maaz

[sudarshan_pavanje]

Hi Nicolas,

Assuming Table A and table B as 2 different data set , the easiest solution i could think was blending.

1) Acquire both the data sets.

2) Link the data sets with the common dimension ie Product.

3) Create a calculated measure for Forecast2016. ( Forecast2016/12)

4) Drag and drop the dimensions/measure to create the visualisations

[Former Member]

Many thanks Maaz and Jay

It is very helpul indeed!

[former_member198672]

Happy to help!

[Former Member]

I faked out your dataset to try this.  I think I got it to work?

Is this what you are trying to do?  If so, here's what I did:

Import data into Lumira as two separate sources

In the Forecast source, I created a new field - M.Forecast, which is just Forecast/12

In the Sales source, I created a new fields - Year (which is the year of the Month field)

I then Merged the two together using Product as my merge key.

Let me know if this is what you were looking for.