Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
load
CustomerID,
Date(RegDate) as RegDate
inline
[
CustomerID, RegDate
1, 2009-01-01
2, 2009-01-01
3, 2009-01-02
4, 2009-01-05
];
Orders:
load
OrderID,
CustomerID,
Date(OrderDate) as OrderDate
inline
[
OrderID, CustomerID, OrderDate
1, 1, 2009-01-01
2, 2, 2009-01-01
3, 1, 2009-01-03
4, 1, 2009-01-04
5, 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!
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:
load
RegDate as Date,
CustomerID,
1 as NbRegs
resident Customers;
concatenate
load
OrderDate as Date,
OrderID,
1 as NbOrders
resident 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
Thanks a lot!
Now I find many post suggest using 'link table' and I'll have a try.
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.