Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi experts,
I have a problem about calculation of a ratio for service units.
I have two tables, one for assembly of the products ("Izlenebilirlik") and one for service calls of these products ("Servis_Fişleri"). This two table is connected with product serial number as you can see from the picture below.
"ServisAdı_" and "Mon_Servisadı" comes from the same table and so has the same service company name dimension.
.
The trouble is that one product can be assembled and repaired (if there is) by different service companies. And I would like to find a process ratio for every service company without restriction of the relation.
Nominater = How many repair were done by service compnay A : Count(ServisAdı_)
Denominater= How many assembly were done by service compnay A :Count(Mon_Servisadı)
Can anyone help on this?
Thanks in advance.
I think this is better, no change in existing model but you need one extra island table as below -
ServiceName:
Load Distinct Assembly_Service_Name as Service_Name
Resident Assemblies;
Concatenate
Load Distinct Repair_Service_Name as Service_Name
Resident Repairs Where Not exists(Service_Name,Repair_Service_Name);
can you share some inline data sample?
I made the table names english for better understanding.
Assemblies:
Load * Inline
[SerialNo, Assembly_Service_Name
1, A
2, A
3, B
4, B
5, B
6, A
7, A
8, B
9, B];
Repairs:
Load * Inline
[SerialNo, Repair_Service_Name
1, A
2, B
3, A
4, A
5, B];
I want to find a ratio for service A = 3/4, and for service B = 2/5. But I can not find these values in the same table to find the ratios
I hope I could explain the situation
Do you want this in KPI or in Table?
I will use it in a table and later in a map.
Map will show the ratios by the service company name.
EDITED
would that work for you
=Count({<Repair_Service_Name={'A'}>}Repair_Service_Name)/Count({<Assembly_Service_Name={'A'}>}Assembly_Service_Name)
=Count({<Repair_Service_Name={'B'}>}Repair_Service_Name)/Count({<Assembly_Service_Name={'B'}>}Repair_Service_Name)
Hi Jahanzeb,
I have a lot of service companies and I need one expression to calculate the ratios for every company.
Ya I was thinking that, try to do this way
if you can add another field just like S.no
this will work with the selection of both repair and assy
Count({<Assembly_Service_Name=>}SerialNo)/Count({<Repair_Service_Name=>}new)
You may need some change in data model to achieve this, one way to do could be like this -
Assemblies:
Load SerialNo&'-'&Assembly_Service_Name as Key,
'A' as AType
Inline
[SerialNo, Assembly_Service_Name
1, A
2, A
3, B
4, B
5, B
6, A
7, A
8, B
9, B];
Repairs:
Load SerialNo&'-'&Repair_Service_Name as Key,
'R' as RType
Inline
[SerialNo, Repair_Service_Name
1, A
2, B
3, A
4, A
5, B];
Hi Jahanzeb,
Firstly, I would like to thank you for your support.
I tried your method and I saw it worked like an "OR" command because of the relation.
While getting company related records in one table, it took the the other companies at the other table.