topic Problem to Sum field at different date in QlikView

Problem to Sum field at different date

giobby85 — Sat, 16 Nov 2024 00:57:09 GMT

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

Re: Problem to Sum field at different date

Taoufiq_Zarra — Sun, 29 Mar 2020 13:27:23 GMT

can you share a sample data and the expected output ?

Re: Problem to Sum field at different date

rubenmarin — Sun, 29 Mar 2020 19:33:46 GMT

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.

Re: Problem to Sum field at different date

giobby85 — Sun, 05 Apr 2020 16:58:30 GMT

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,

....

Re: Problem to Sum field at different date

rubenmarin — Sun, 05 Apr 2020 18:29:44 GMT

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,