cancel
Showing results for 
Search instead for 
Did you mean: 

Average Calculation - SAP Lumira

Former Member
0 Kudos

Hello Experts,

I am pretty new to SAP Lumira and Visualization tools. I am trying to create Visualization for Order Bookings.


I have order bookings data for the year 2015 ( Pvs Year) and 2016 ( Current Year) with columns :  “Customer Name”; “Fiscal Month”; “Fiscal Week”; “Fiscal Year”; “Day of the week” and “Order Amount”.


My objective is see the customer Order behavior:

  • By calculating Simple average “Year over Year”; “Week over Week” and “Day wise” to see the trend of booking.

E.g. I have data for 12 Months; 52 Fiscal weeks for the year 2015 and only 1 month and 4 Fiscal week data for the year 2016.


1) I would like to see the average of 1 to 52 Fiscal weeks of the year 2015 and compare the same with the actuals of the year 2016.

2) I would like to see the average of 1 to 12 Fiscal months of the year 2015 and compare the same with the actuals of the year 2016

3) I would also like to see Day wise comparison i.e. Average of only Monday’s from 1 to 52 the fiscal week (2015) Vs current year (2016) actuals, similarly want to do the calculation for rest of the days in week.

4) The final one would be on a Tuesday I would like to see Sum of (Mon + Tue), on a Wednesday would like to see sum of (Mon + Tue + Wed) by using the current year data.


I started exploring options in Lumira and created few visualization, but not successful in creating as per the requirements mentioned above.


While surfing I saw a post “Lumira Hidden Gems: Average is not so Average”. Posted by “Dean Farrow” (http://scn.sap.com/community/lumira/blog/2015/04/20/lumira-hidden-gems-average-is-not-so-average).  Which partially answered my question 1 and 2.


I used only 9 months and 39 fiscal weeks’ data. By using the steps mentioned in the post, I could see the actual “Order amount” with a straight line average plotted across for Fiscal Month and Fiscal Week respectively. Screen shots in the attached doc file.


Challenges Faced:

As per my requirement I want to compare Fiscal Year 2015 and Fiscal Year 2016. I have the data in two different sets. As mentioned earlier I want average of 1-52 weeks of Fiscal year 2015 only to compare the same with 2016.


But, when I append fiscal year 2016 data with 2015 data, the average is calculated for 52 weeks of 2015 and 4 weeks of 2016 I.e.(Total Order amount/56 weeks).

I am stuck here not able to calculate Average only for the year 2015 and show straight line average plotted across with actual Order amount of the year 2016.


Question 3:

I do not have any clue how to go about this requirement. I used the below mentioned syntax to get only Monday’s. But not successful as it will calculate the numbers including Null.


DayOfWeek ({Transaction Date})

If DayOfWeek ({Transaction Date}) = 1 then "Mon" else "Null"


Question 4:

The only option I could think of is filtering the “Day” i.e. on a Wed I would filter (Mon + Tue + wed)


I am clueless, and this is my first real foray into visualization tool so it is fairly new to me. I have attached the word file with the same explanation along with the screen shots.


Any suggestions would be greatly appreciated!


I am looking forward for the positive response!!!!!!


Thanks,

Ganesh



Accepted Solutions (1)

Accepted Solutions (1)

TammyPowlas
Active Contributor
0 Kudos

Hi Ganesh,

Before diving into your details, have you seen this document by Andreas Krause about year-to-date and period-to-date calculations?  Will that help guide you?

Former Member
0 Kudos

Hi Tammy,

Thanks a ton for the quick response!

I am going through the calculations posted by Andreas Krause about year-to-date and period-to-date now. Our fiscal calendar is from October to September, hence we will have Dates from 1-10-2014 to 30-9-2015 under fiscal year 2015. I am not sure if Current date syntax will help me.

However I will try to use "Fiscal Year" column to bifurcate Year 2015 and 2016.


In the meanwhile if i calculate Average based on the output of this function, will it consider 0 in the calculation?


For e.g. 5 0 5 0 5 = 15/5=3, But i would need 15/3=5

            

Thanks,

Ganesh



TammyPowlas
Active Contributor
0 Kudos

Hi Ganesh - why not try it yourself?

Former Member
0 Kudos

Sure I will try... If i hit a road block i will seek help from the forum...

Thanks again:)

Former Member
0 Kudos

Hi Tammy,

Thanks a lot  for recommending to refer document posted by "Andreas Krause". With article “Lumira Hidden Gems: Average is not so Average”. Posted by “Dean Farrow”  I am able to calculate all the fields i needed.


However if i use 0 in any function:


For e.g. In the below mentioned graph I have data for 12 Months for the year 2014 and only 9 months  for the year 2015.

I do not want to show month 10 ,11 and 12 in the graph.

Is there any function i can use instead of 0, Null function is not working here... I am not able find anything.

If i exclude 10 , 11 ,12 The average for 2014 is changing, which i don't want to change

Your help will be greatly appreciated!

Thanks,

Ganesh

Former Member
0 Kudos

Hello All,

Is there any way to exclude o in the graph without changing average for 2014.

Thanks,

Ganesh

TammyPowlas
Active Contributor
0 Kudos

Did you try it yourself?  What were the results?

Former Member
0 Kudos

Yes, I tried.. if i exclude 0's in the below mentioned graph for the month (10 , 11 ,12) The average for 2014 is changing, which i don't want to change.

The reason i want to exclude 0's is, in the current year i have data only for 9 months and i don't want to show anything for the month 10 ,11 and 12 under the graph

For e.g. In the the above mentioned graph The average for 2014 is 178 Million (I.e. Total Order amount/12)

In the below mentioned graph i have excluded 0's ( 10, 11 and 12), The average for 2014 now is 176 Million (i.e. Total Order amount /9)

Thanks,

Ganesh

TammyPowlas
Active Contributor
0 Kudos

Inside the graph, could you select the item you want to exclude, and then see if the averages change?

My data is different than yours, but my averages aren't changing.  There could be some differences between our set-up

Former Member
0 Kudos

I did the same, But Averages are changing.. I used moving average method, checking Include self for calculating averages. I have data around 600,000 lines, this method helps me calculate Order Amount/12 or else the default average calculation will give me Order amount/ 600,000 lines. Below is the screen shot for your reference.



Please do let me know if there is any other method to calculate average. Where Average numbers do not change if i exclude 0's

Thanks,

Ganesh


TammyPowlas
Active Contributor
0 Kudos

Hi - I didn't realize you were using a moving average calculation

I was using the average in the aggregations - see below:

I am not sure it's possible to do what you are trying. 

Former Member
0 Kudos

No I cant use average in the aggregations, as i mentioned in my earlier comment it calculates Total amount/ No of lines not number of months.

Thanks,

G

Answers (0)