Duplicates data

[guillaume_gorli]

Dear All,

I am working with data for wich i have duplicates informations.

Fields of the database being :

Date as extraction date

part_no as part number

cost as unit cost

bin as physical localisation

Qty as quantity in stock

Future Usage as quantity identified to be consummed

Family as a specific category

Issue is that field "Future Usage" is linked to the part number and not to the bin location. For instance 5 units of part number A are identified to be consummed in total (not 10) although 5 units are in stock in total (2+3)

Target is to extract from the database and value the "available stock value" which means basically Sum (Qty) - Num ([Future Usage]) * num (cost)

First i managed to gather all the informations i need in a chart using the field "part_no" as a key using the expression (Num (TotalQty) - Num ([Future Usage]))*Num (cost) to identify the available stock value :

it works fine when building a chart using field "part_no" as a key but i cannot make it using a criteria like field "Date" :

What i need to produce would be a simple chart showing :

1. Total stock value
2. Total stock value for family BLUE using set analysis
3. Total available stock value
4. Total available stock value using set analysis

I am struggling with the formulas for the available stock value as the formulas (Num (TotalQty) - Num ([Future Usage]))*Num (cost) is not working at all.

For those who wants to help, i attach qvw as well as excel test database

Thanks in advance

Guillaume

[sunny_talwar · Accepted solution]

This?

Available Stock Valu Expression:

Sum(Aggr((Num(TotalQty) - Num([Future Usage])) * Only(cost), part_no))

Stock Available Value BLUE

Sum(Aggr((Only({$ <Family = {BLUE}>} TotalQty) - Only({$ <Family = {BLUE}>}[Future Usage])) * Only({$ <Family = {BLUE}>}cost), part_no))

[guillaume_gorli]

Perfect Sunny !

Thanks a lot

[sunny_talwar]

Not a problem. I am glad I was able to help.

Best,

Sunny