I have a seemingly simple issue, but despite trying a lot, I have not found a solution to that.
Here's the stripped down question:
Assume, that I am producing Cars with serial numbers. They are listed in a table "Cars", and have (amongst other data) the Fields Serial and FabricationDate.
Cars:LOAD * INLINE [
Serial, FabricationDate
1, 01.01.2018 2, 01.01.2018
3, 02.01.2018
4, 02.01.2018
5, 02.01.2018
6, 03.01.2018
7, 03.01.2018
];
For each of my Cars, I have different Parts used during production; not every car gets all PartsUsed, so I have a table, connecting the Serial with the Material (Foo, Bar, or Toast) and the Cost of this Part (in the example, each Material has a fixed price, but in reality, it is flexible, as it may contain actual assembly time)
In that table, only actually used parts are listed; If I don't use "Bar" for Serial 1, there is no row containing Serial=1 and Material = "Bar"
PartsUsed:
LOAD * INLINE [
Serial, Material, Cost
1, Foo, 10
1, Toast, 30
2, Bar, 20
2, Toast, 30
3, Bar, 20
3, Toast, 30
4, Foo, 10
4, Bar, 20
4, Toast, 30
5, Foo, 10
5, Bar, 20
5, Toast, 30
6, Foo, 10
6, Bar, 20
6, Toast, 30
7, Foo, 10
7, Bar, 20
7, Toast, 30
];
So now I want to see the average cost per day, and I make a table containing
Dimension1: FabricationDate, Dimension2: Material, Measure: Avg(Cost)
So, Qlik does the average over the existing materials per Serial, but does not consider Materials not used for that Serial.
(For Day 01.01.2018, I would like to have something like 3.33 (=10/3) for Foo, 13.333 (=(20+20)/3)for Bar, and 30 (3*30/3) for Toast, but the result looks like this: )
| FabricationDate | Material | avg(Cost) |
|---|
| 01.01.2018 | Toast | 30 |
| 01.01.2018 | Bar | 20 |
| 01.01.2018 | Foo | 10 |
| 02.01.2018 | Toast | 30 |
| 02.01.2018 | Bar | 20 |
| 02.01.2018 | Foo | 10 |
| 03.01.2018 | Toast | 30 |
| 03.01.2018 | Bar | 20 |
| 03.01.2018 | Foo | 10 |
I've fiddled around with doing the average on my own, or using AGGR, but I have not found a solution.
Maybe someone can help me out? Should be a common issue, I would guess, and I am just blindfolded...
(Do I need to artificially create all Materials for each Serial? How would I do that, if the Materials list may be expanding over time?)
Thank you very much in advance!
