on 03-05-2018 12:23 PM
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
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?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
25 | |
12 | |
9 | |
6 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.