topic SV:Show data in one chart based on different dimensions in QlikView

Show data in one chart based on different dimensions

Mon, 25 Jan 2010 07:26:28 GMT

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!

SV:Show data in one chart based on different dimensions

Mon, 25 Jan 2010 10:57:29 GMT

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

Jakob Berglund

MILLNET BI

SV:Show data in one chart based on different dimensions

Fri, 23 Apr 2010 07:34:06 GMT

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

johnw — Sat, 24 Apr 2010 00:50:07 GMT


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.