selection of all data, based on a specific field in pivot table

[#1]

I have 2 kinds of facts (concatenated):

orders and stock

In an overview of orders, I need to see a calculated field per orderline indicating the total amount of stock available for that product

Data is like (- = no value)

Type, Order, Customer, Product, StockQuantity, OrderQuantity

'Stock',-,-,A,10,-

'Order',1,CustomerA,A,-,2

How can I make a sum of all stock records in a pivot table, knowing that I need type "Stock", for the current product on each line, but independent on the selected values for customer & order?

I know I can select using "$" (current selection), and set Customer and Order dimension to empty, but then I need to know at each time what fields are in the pivot table and affecting my current selection. I rather not use this solution.

I'm looking for a solution like sum({1<Type={'Stock'}, Product={CURRENT_PRODUCT_FOR_ORDERLINE}>} StockQuantity)

best regards,

Jan

[#2]

I added a test project and a screenshot with what i get now (and is wrong), and what i want...

[#3]

Nobody?

[ashfaq_haseeb]

Hi,

There is no customer associated.

Instead of Concatenate try by joining.

Regards

ASHFAQ

[Anonymous · Accepted solution]

You also need to rename one of the [Type] fields to make the join work properly.

facts:

LOAD * INLINE [

    Type, Product, QtyStock

    Stock, A, 10

    Stock, B, 20

    Stock, A, 3

];

join (facts)

load

    Type as Type2 ,

    Customer,

    Order,

    Product,

    QtyOrdered

;

LOAD * INLINE [

    Type, Customer, Order, Product, QtyOrdered

    Order, John Deer, 0001, A, 5

    Order, John Doe, 0002, A, 3

    Order, John Doe, 0002, B, 5

    Order, John Ny, 0003, A, 10

];

[#6]

Is this the only way possible?

I rather have a solution where i can simply execute a query on all the data in the datamodel, based on specific criteria that I define, and with one of the criteria being mapped to the Product key from my current selection

Does this make sense?

maybe some pseudo SQL shows more what I want to achieve:

select

     customer

     , order

     , product

     , sum(QtyOrdered) as QtyOrdered

     , (select sum(QtyStock) from Stock where Product = O.Product) QtyStockSummed

from

     Orders O

[Anonymous]

Jan

I would personally join in the load script to create a suitable data model.

It may well be possible to get the answer you are after from your original data model, but that would be an unnecessary wrestle.  So I would recommend do it the easy way & join.