Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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:
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!

3 Replies
Not applicable
Author

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

Not applicable
Author

Thanks a lot!

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

johnw
Champion III
Champion III


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.