Hello,
I have two tables, one containing the details of every machine which isn't working and the reported date of the status (as well as Country, Product Line, etc). I also have another table with the total amount of machines (installed base) per Region and Product Line at certain periods (I have a "from" and "to" date for each Region-Product Line combination). I'm linking both tables per Region-ProductLine. What I want to do is divide the amount of non-working machines in a certain month by the installed base in the period that month is located into. I've tried the following expression but it ain't working:
sum ( { < IB.FromDate={"<=Calendardate"}, IB.ToDate ={">=Calendardate "} >} distinct IB)
The tables I have look like the following:
Table 1:
| Machine ID | Region | Product Line | Calendardate | Link |
|---|
| 1 | America | A | 03/08/2014 | AmericaA |
| 2 | Europe | B | 08/08/2014 | EuropeB |
| 3 | America | B | 15/08/2014 | AmericaB |
| 4 | America | B | 25/09/2014 | AmericaC |
Table 2:
| Link | IB.FromDate | IB.ToDate | IB |
|---|
| AmericaA | 01/11/2013 | 01/09/2014 | 10 |
| AmericaB | 01/11/2013 | 01/09/2014 | 8 |
| EuropeA | 01/11/2013 | 01/09/2014 | 5 |
| EuropeB | 01/11/2013 | 01/09/2014 | 7 |
So what I'd want is to get the following table for AUGUST:
| Region\Product Line | A | B |
|---|
| America | 1/10 | 2/8 |
| Europe | 0/5 | 1/7 |
Please help me solve this issue and get the right formula. Thanks a lot in advance
