Problem to Sum field at different date

[giobby85]

Hello,  I am new in qlik.

I am developing a report to summarize some metrics (table "Report"). I have a problem with the total cost of material.  Each products has costs, table "Products" shows that product 110101 has at 2013 a costs and at 2014 costs.  In report I would sum the total cost of the products for a specific YearsInvoiceDate(example 2013), in reality, It sum both the costs at 2013 and 2014 for a specific "YearInvoicedate". YearInvoceData is a fiel into table "Invoice"
The cost of material expression is Sum( [Invoiced Quantity]* [Matirial Cost] ).

Thank you

[Taoufiq_Zarra]

can you share a sample data and the expected output ?

[rubenmarin · Accepted solution]

Hi, I think this will be a lot easier if you create the cost in script, you can do a mapping table to apply costs:

// Load costs from products table
mapProductCost:
Mapping LOAD
  ProductID &'_'&YearProductCost,
  ProductCost
Resident Products;

//Apppy cost when loading invoices
Invoices:
LOAD ..., // fields
  [Invoiced Quantity]* Applymap('mapProductCost', [Product ID]&'_'&YearInvoicedate) as ProductCost
From/Resident...

Field names for invoices may be others, it should be the name they have in origin.

[giobby85]

Looks a nice solution but does not work. it says Field 'YearInvoiceData' not found, maybe because, I define yearInvoiceData as year([Invoice Data])

My code:

Mapping ...

InvoicesHeader:
LOAD [Invoice Number],
...

Year([Invoice Date]) as YearInvoiceData
FROM

.......

Invoices:
LOAD [Invoice Number],

[Invoiced Quantity]* Applymap('mapProductCost', [Product ID]&'_'& YearInvoiceData) as ProductCostYear,

....

[rubenmarin · Accepted solution]

Hi, then use "year([Invoice Data])": // It should be Date instead of Data?

Invoices:
LOAD [Invoice Number],

[Invoiced Quantity]* Applymap('mapProductCost', [Product ID]&'_'& year([Invoice Data])) as ProductCostYear,