Calculation from 2 tables

jimbo_carlos — Thu, 28 Feb 2019 10:48:56 GMT

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

DATE	CAR TYPE	Spare Cost
01-Nov-18	R001	380
31-Oct-18	R000	380
31-Oct-18	R000	380
31-Oct-18	R001	380
31-Oct-18	R000	380
31-Oct-18	R001	380
31-Oct-18	R000	380
31-Oct-18	R000	380
31-Oct-18	R001	380
23-Sep-18	R000	380
23-Sep-18	R000	380
23-Sep-18	R001	380
23-Sep-18	R001	380
23-Sep-18	R000	380
23-Sep-18	R000	380
23-Sep-18	R001	380
23-Sep-18	R001	380
23-Sep-18	R000	380

Table 2

DATE	CAR TYPE	Number of used part
01-Nov-18	R001	1
31-Oct-18	R001	1
31-Oct-18	R000	1
31-Oct-18	R000	1
31-Oct-18	R001	1
31-Oct-18	R000	1
31-Oct-18	R000	1
31-Oct-18	R000	1
31-Oct-18	R000	1
31-Oct-18	R001	1
23-Sep-18	R000	1
23-Sep-18	R001	1
23-Sep-18	R001	1
23-Sep-18	R000	1
23-Sep-18	R000	1
23-Sep-18	R001	1
23-Sep-18	R000	1
23-Sep-18	R001	1
23-Sep-18	R000	1
23-Sep-18	R000	1
23-Sep-18	R000	1

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

Sept:

Oct:

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

Thank you for your time and help on this..

Jimbo

Re: Calculation from 2 tables

NahumMC — Mon, 04 Mar 2019 10:12:43 GMT

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:

Hope it could help you.

Best Regards.

Nahúm.

Re: Calculation from 2 tables

rahulpawarb — Mon, 04 Mar 2019 10:22:13 GMT

Please refer attached application.

topic Re: Calculation from 2 tables in QlikView

Calculation from 2 tables

Re: Calculation from 2 tables

Re: Calculation from 2 tables