Skip to Content
avatar image
Former Member

Combining data from two sources with with no common reference

Hi there,

I am hoping that someone can assist with a query that I have with a WEBI report that I am building.

My intention is to build a report that allows me to calculate “Day Sales Outstanding” which, for a particular counterparty is:

BALANCE SHEET DEBT / 12 MONTH REVENUE * 365

This essentially gives a measure of how quickly a company is paying its bills.

I have two separate queries, one for debt and another for revenue. Presentation ally, these are formatted in a very similar fashion:

Query 1: DEBT

Dimension: Dimension: Measure:

Company Name Snapshot balance sheet date Value of debt

Company 1 0117 £1000

Company 1 0217 £1500

Company 1 0317 £3000

Company 2 0117 £4000

Query 2: Revenue

Dimension: Variable: Variable:

Company Name Month invoice sent Total revenue amount (12 months rolling)

Company 1 0117 £10,000

Company 1 0217 £40,000

Company 1 0317 £70,000

Company 2 0117 £25,000

What I would like to be able to do is calculate for each month, what the day sales outstanding value is for each company was . E.g. As at February 17, Company 1 DSO is (1500 / 40,000) * 365 = 13.7 days.

Problem

The issue that I have is bringing the two sets of data together. I can merge on the Company name as it appears in both queries. Unfortunately, whilst both “Snapshot balance sheet date” and “Month invoice sent” appear visually to be the same (e.g. 0217), I can’t figure out a way of telling WEBI this. Very frustrating!

Any guidance on this would be greatly appreciated.

Thanks,

Mike

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Mar 05 at 08:37 PM

    is it possible to create dimension object in the universe for "Month invoice sent" similar to the "Snapshot balance sheet date" dimension object values format.After that you can merge both objects and can use straightforward in the report.

    or i can see values format is same both in dimension and variable.have you tried to drag "Snapshot balance sheet date" object in the table?

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Mar 06 at 12:09 PM

    Hi Amit,

    Thanks for the response.

    Actually I figured it out, I created a detail variable as follows:

    DEBT - Detail variable (snapshot balance sheet date) with the Company Name as the associated dimension.

    REVENUE - Detail variable (month invoice sent) with the Company Name as the associated dimension.

    I could then merge on the detail variables and get my report to work :) Happy days.

    Add comment
    10|10000 characters needed characters exceeded