Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
];
I added a test project and a screenshot with what i get now (and is wrong), and what i want...
Nobody?
Hi,
There is no customer associated.
Instead of Concatenate try by joining.
Regards
ASHFAQ
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
];
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
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.