Problem with calendar?

[sebastian_fager]

Hi

I have the following problem:

Example: i have 3 tabels, two from the database and one mastercalender.

I want to to compare Salesamount and Orderamount in a diagram. But I miss I few Orderamount if i haven't sold the product the same day as the order.

How do i solve this?

Sales:

ProductID,

Date,

SalesAmount,

ProductID&'-'&Date as SalesOrderKey

Order:

//ProductID,

//Date,

OrderAmount,

ProductID&'-'&Date as SalesOrderKey

Calendar:

...

Date

Week

Year

[MK_QSL]

Probably you need to create canonical date calendar.

Canonical Date

[sebastian_fager]

Hi could you help me, don't really understand how to do this.

Have following tables from different database.

Sales:

ProductID,

Date,

SaleAmount

Order:

ProductID,

Date,

OrderAmout

Warehouse:

ProductID,

Date,

InventoryValue

How do i combined this with one calendar?

[jonathandienst]

In this case, it is usually better to create a single fact table for noth Orders and Sales like this

SalesAndOrders:

LOAD

  ProductID,

  Date,

  SalesAmount as Amount,

  ...,

  'Sale' as TransactionType

FROM ...

Concatenate(SalesAndOrders)

LOAD

  ProductID,

  Date,

  OrderAmount as Amount,

  ...,

  'Order' as TransactionType

FROM ...

Calendar:

...

Date

Week

Year

Now both are linked into the calendar, and you can select the type using a simple set expression:

=Sum({<TransactionType = {'Sale'}>} Amount)

=Sum({<TransactionType = {'Order'}>} Amount)

[sebastian_fager]

Hi,

And then do the same fore the warehouse?

[sebastian_fager]

Hi,

I successfully Concatenate the 3 tables to one! Now i one problem left.

I want to calculate the total cost value of the Sales, Orders and Warehouse.

The cost is per productID which i have in a product-table.

If i do this:

SalesOrderAndWarehouse:

ProductID

Date

Amount

From...

Product:

ProductID

Cost

From...

I only get the value for the selected product in a line-diagram, but i also want the sum for all products.

Have tried the following

(Sum({<Typ = {'Warehouse'}>} Amount)*Cost

(Sum({<Typ = {'Warehouse'}>} Amount)*aggr(Cost,ProductID)

In a tabel with the total function sum seams to work..