Bar Charts

[pkelly]

I have two bar charts (please attachement for mock up)...

We are trying to analyse the perfromance of new accounts...

Chart A is a cumulative chart which shows # of new accounts added by month from a specific period.

Chart B shows how many of these new accounts had had a sale split by month - if account A has had a sale in January and February it will show 1 in each column.

I have been asked if it is possible to merge the two graphs.

The issues that I have is that both work from different dimensions...

Chart A work from the customer master date on file whilst Chart B works from the Invoice Month.

Is there any way that I can merge these two graphs together.

Any help greatly appreciated...

[vidyut · Accepted solution]

Hi,

It is possible to merge both of these. As I understand, you have two date dimensions, one for Invoice date and another relating to the Customer Start date. So, understandably, two fact tables to show the data from.

You may be using accumulation in the first chart making it a bit more tricky.

You could create a independent Date Dimension (Not linking with any other table in the model), and use that as the Dimension in the new chart. For the first expression, compare the Customer Start date to the new date dimension, and for the second, compare the invoice date to the new date dimension.

For example, if your first expression was Count(Distinct CustomerID) and Dimension was CustomerStartDate, You could make it like:

Count( Distinct If(CustomerStartDate<=IDate,CustomerID))

and for the second, if your expression was:

Count(InvoiceID)

Then change it to:

Count(If(InvoiceDate=IDate,InvoiceID)

and keep IMonth_Yr as your dimension in the chart.

[#3]

Hi,

Can you please explaine how you create an independent Date Dimension. Is it done in the script?

Thanks

[#4]

Hello holgardj,

yes, you create it as a table in the script. See the following code snippet for details and to gather ideas from.

SET vStart = 40178; // QV-internal rep. of date

RawDate:
Load
 date( $(vStart) + rowno()) AS RawDate
autogenerate(365);

DateTbl:
Load
 num(RawDate) AS $Date_ID,
 RawDate AS Date,
 Month(RawDate) AS Month,
 IF(Month(RawDate) < 4, 'Q1',
 IF(Month(RawDate) < 7, 'Q2',
 IF(Month(RawDate) <10, 'Q3',
 IF(Month(RawDate) <13, 'Q4', 'Q5')))) AS Quarter,
 Year(RawDate) AS Year,
 dual(Month(RawDate) & '-' & Year(RawDate), Year(RawDate) & Month(RawDate))
 AS MonthYear
Resident RawDate;

Regards, Roland