I have the following issue, I try to combine Excel data with data from a UNX universe in BO 4.1 SP5. I added the Excel file to Launchpad to be able to combine the Excel data and the Universe data.
the Excel data contains labor hour rates and the universe contains the actual hours. I can combine the data if I have one hourly rate set in the Excel file. But what I would like to do is having date ranges in the Excel file because the hourly rate can change every couple of months. And combine that with the dates in my database.
Is it possible when I rollup the hours over different months do this:
So I have this data in Excel:
yearmonth, operation, labor rate:
201601 O1 10
201602 O1 20
what currently happens is:
Operation, hours labor rate
O1 4 4 * 10 (201601) + 4 * 20 (201602) = 120
What I would like is:
Operation, hours labor rate
O1 4 2 * 10 (201601) + 2* 20 (201602) = 60
is there a formula so I can do the above calculation?
I hope I make myself clear, let me know otherwise.
Sorry for the confusion. But what I am trying to achieve is to combine my data from the excel file with data from my database. If possible date/time periods would be the best solution.
But I cannot merge on date/time stamps because then I would need to use a between on my report and I don’t think that that is going to work. Because when I have two time periods in my excel file, I will get an multi value error in my report, because it doesn’t know how to merge the data.
And if I use multiple year month periods I get the issue as shown above if I have the same measure for the same dimension. See the issue described above.
So then I think the only working solution would be to put it somewhere in the database, but I do not have access to achieve that.
Are rates in the spreadsheet only ever held at year month level?
If that is the case and you can identify the year month of work (potentially a new object in the universe but not a difficult one to build) then you can bring year month back and merge them as well as merging on operation - you then have the rate for the year month multiplied by the hours worked.
i tried a couple of things the last week, but I still have an issue.
What I have tried so far is:
Added the data in a table in a separate database. It is not possible for me to put this in the same database. So I still have to merge the data on the report.
When I have a data set like this, see screenshot 1.
I can merge this on the report. See screenshot 2.
For the final result I would like to achieve see screenshot 3. But because my data is shown in two months I get this multivalue issue.
I connected my two queries on these fields:
in the query panel I used the combine use the result from another query.
co assembled Date equal to Date
Component equal to co component
Is there a way to combine the rows in BO or in the universe? I am using the UNX universe. I don’t have permission to use the IDT universe.
But what I get now when I remove the date field in the report for a roll up I get a Multivalue error.
Is there a way to show the correct value in the table?
(BOB member since 2010-05-03)
(BOB member since 2010-05-03)