Skip to Content
0

How can I use Excel for quota and Bex Query for actuals in Webi?

Dec 30, 2016 at 02:35 PM

58

avatar image
Former Member

I have a simple column chart in Excel that shows Quota and Actual sales going back to 2010, by year. I also have 2016 quota information in the Excel file.

I want to use my Bex Query data to fill in the actual sales for 2016 in the same chart.

How can I set this up so that it works?

pic-1.png (12.9 kB)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

6 Answers

Best Answer
AMIT KUMAR
Dec 30, 2016 at 07:27 PM
0

Add 01/01/2014 in the excel and column type as date.

After that you can merge both the objects and later change the format of your merged object to YYYY only or use Year() function to get the year only

Share
10 |10000 characters needed characters left characters exceeded
AMIT KUMAR
Dec 30, 2016 at 04:54 PM
0

use two queries one from excel and one from BEx in webi.merge the dimension object which may be your Year object.

For measure object create variables to clud the data from both queries.

Var actual=excel actual+Bex actual

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Dec 30, 2016 at 07:14 PM
0

Thanks Amit,

I have tried that and the year will not merge. The Year in the EXCEL file is 2014, 2015,2016 etc.

The date field in the BEX query is mm/dd/yyyy so when I try to merge it, the option is greyed out...

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Dec 30, 2016 at 07:50 PM
0

Let me try

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Dec 30, 2016 at 07:57 PM
0

Ok I have the values charted for the years as category axis, I also have the quota data values loaded from the Excel sheet.

My next question is how to get the 2016 actual sales into the chart while using the Excel sheet for the previous years actuals data?

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Dec 30, 2016 at 08:11 PM
0

Amit, I think I have it figured out, thanks for your help!

Share
10 |10000 characters needed characters left characters exceeded