3 Replies Latest reply: Apr 23, 2010 8:50 PM by John Witherspoon

# Show data in one chart based on different dimensions

Hi All,

A question about set analysis -- "show data in one chart based on different dimensions".

Anyone can help? Thanks a lot!

The basic tables look like following, and you can change.

`Customers:loadCustomerID,Date(RegDate) as RegDateinline[CustomerID, RegDate1, 2009-01-012, 2009-01-013, 2009-01-024, 2009-01-05];Orders:loadOrderID,CustomerID,Date(OrderDate) as OrderDateinline[OrderID, CustomerID, OrderDate1, 1, 2009-01-012, 2, 2009-01-013, 1, 2009-01-034, 1, 2009-01-045, 3, 2009-01-04];`

What I want to see is # of Registers and # of Orders by day (register date and order date)

Date, NbRegs, NbOrders
2009-01-01, 2, 2
2009-01-02, 1, 0
2009-01-03, 0, 1
2009-01-04, 0, 2
2009-01-05, 1, 0

Could anyone help to provide the expresssion?

Thanks again!

• ###### SV:Show data in one chart based on different dimensions

You don't need set analysis if you just create a new dimension named "Date" based on a concatenation of the two "RegDate" and "OrderDate". Then count the number of CustomerID and OrderID for each Date.

A quicker way than count is to use sum, see attached example.

`Statistics:loadRegDate as Date,CustomerID,1 as NbRegsresident Customers;concatenateloadOrderDate as Date,OrderID,1 as NbOrdersresident Orders;//the expressiones used to count NbRegs and NbOrders in the table later is sum(NbRegs) and sum(NbOrders) with the dimension Date//alternately, the slower way to calculate is to use count(distinct OrderID) and count(distinct CustomerID) with the dimension Date`

BR

Jakob Berglund

MILLNET BI

• ###### SV:Show data in one chart based on different dimensions

Thanks a lot!

Now I find many post suggest using 'link table' and I'll have a try.

• ###### SV:Show data in one chart based on different dimensions

`Jakob Berglund wrote:A quicker way than count is to use sum`

That seems to depend on the version.

I forget which versions I tested, and which direction it was in each case, but in one version I tested, count() was faster (which I would expect), and in the other, sum() was faster (which seems unusual to me). In any case, if performance is critical, you'll want to test both count() and sum(). If performance isn't critical, I'd just use count(), because it seems more obvious so should therefore be slightly easier for less experienced people to maintain.