Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
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.