Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
jimbo_carlos
Contributor II
Contributor II

Calculation from 2 tables

Dear all,

Apologize for the long post here. I am new to the QlikView and experienced some difficulties to generate below result.. I have two tables. I would like to calculate the sum of 2 column by type and date.

For example, for each car type,  I wanted to calculate Total of Cost/Total of  Parts, grouped by date and make a chart.

Table 1

 DATECAR TYPESpare Cost
01-Nov-18R001380
31-Oct-18R000380
31-Oct-18R000380
31-Oct-18R001380
31-Oct-18R000380
31-Oct-18R001380
31-Oct-18R000380
31-Oct-18R000380
31-Oct-18R001380
23-Sep-18R000380
23-Sep-18R000380
23-Sep-18R001380
23-Sep-18R001380
23-Sep-18R000380
23-Sep-18R000380
23-Sep-18R001380
23-Sep-18R001380
23-Sep-18R000380

 

Table 2

DATECAR TYPENumber of used part
01-Nov-18R0011
31-Oct-18R0011
31-Oct-18R0001
31-Oct-18R0001
31-Oct-18R0011
31-Oct-18R0001
31-Oct-18R0001
31-Oct-18R0001
31-Oct-18R0001
31-Oct-18R0011
23-Sep-18R0001
23-Sep-18R0011
23-Sep-18R0011
23-Sep-18R0001
23-Sep-18R0001
23-Sep-18R0011
23-Sep-18R0001
23-Sep-18R0011
23-Sep-18R0001
23-Sep-18R0001
23-Sep-18R0001

 

Result that we want to achieve. 1) Group by month:

Sept:

Sept.jpg

Oct: 


oct.jpg

2) And the graph to be presented into this way:gr.jpg

 

Thank you for your time and help on this..

Jimbo

2 Replies
NahumMC
Contributor II
Contributor II

Hi Jimbo,

Just create in script by Month function "Month(Date)" a field to take the month and use it as dimension.

Then create a line chart and dimension by Month and Car Type.

Make an expression: sum(SpareCost)/Sum(NumberOfUsedParts).

Then you will have something like this:

 

ExampleChart.png

Hope it could help you.

Best Regards.

Nahúm.

rahulpawarb
Specialist III
Specialist III

Please refer attached application.