Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Girls and Guys,
I want to make 3 KPI's out of the following data:
1. the sum of Colum O. In this example: 4
2. the count of Colum O, or the sum of 1 and 0 in Colum O. In this example : 6
3. The result of the deviation (4/6) in this example: 66,67%
See attachement for the example-data. Unfortunatly i cannot share a QVD.
Hope you want to help me ...
Thanks for your reaction. Its not as simple as it seems i beleef.
In the tabel i put in this formula per line:
IF
((
IF(OrderingUnit=('ST'),
Sum([RegAantal])
/
SUM(if(aggr(min(OrderNummer),Leverdatum,DebiteurNr,ArtikelNummer), Aantal))
,
IF(OrderingUnit=('KR'),
Sum([RegAantal])
/
SUM(if(aggr(min(OrderNummer),Leverdatum,DebiteurNr,ArtikelNummer), Aantal))
,
Sum([NettoKg])
/
SUM(if(aggr(min(OrderNummer),Leverdatum,DebiteurNr,ArtikelNummer), Gewicht))
))>0.95
and
(IF(OrderingUnit=('ST'),
Sum([RegAantal])
/
SUM(if(aggr(min(OrderNummer),Leverdatum,DebiteurNr,ArtikelNummer), Aantal))
,
IF(OrderingUnit=('KR'),
Sum([RegAantal])
/
SUM(if(aggr(min(OrderNummer),Leverdatum,DebiteurNr,ArtikelNummer), Aantal))
,
Sum([NettoKg])
/
SUM(if(aggr(min(OrderNummer),Leverdatum,DebiteurNr,ArtikelNummer), Gewicht))
))<1.05)),
1,0)
If i put this formula in a KPI then it gives me a 0 or 1 depending on the outcome of the lines. I can not just put in between sum() because then it says: Error in expression: Nested aggregation not allowed