Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

Problem to Sum field at different date

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

domanda.png

Labels (3)
2 Solutions

Accepted Solutions
Highlighted

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.

View solution in original post

Highlighted

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,

View solution in original post

4 Replies
Highlighted
Master
Master

can you share a sample data and the expected output ?

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Highlighted

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.

View solution in original post

Highlighted
Partner
Partner

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,

....

 

Highlighted

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,

View solution in original post