Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
Please refer attached application.