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 "} >} distinctIB)
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
