Draw two lines in one line chart

[wanyunyang]

Hi all,

I have a table in my model giving:

Customer, Item, OrderYear, ShippingYear

I need to draw two lines: one gives count of items based on OrderYear, the other gives count of items based on ShippingYear.

My question is: Is it possible to draw these two lines in one line chart?

Thank you in advance!

[pedrobergo · Accepted solution]

Hi @wanyunyang 

You need to separate the facts (or events). One fact for ordering and another one for shipping, then creating a single calendar. To do that, first separate the dates into two datasets:

Orders is your today´s table.

OrdersDates will have two datasets, one for OrdersYears and other one with ShippingYears, but using only one field, let´s call it CrossYear.

Creating the OrdersDate table:

OdersDates:
Load OrderID,
     'Ordered' AS Type,
     OrderYear as CrossYear
Resident Orders;
Load OrderID,
     'Shipped' AS Type,
     ShippingYear as CrossYear
Resident Orders;

You must connect OrdersDates to Orders table with a single key, I used OrderID.

Now, you can create two formulas on charts using Type field to filtering info.

OrderedItems: 

Count({<Type={'Ordered'}>} Item)

ShippedItems:

Count({<Type={'Shipped'}>} Item)

And use the new year field on chart or table:

CrossYear

[],

Pedro