In qlikview, i have created two table, one table which showing the details of out of stock(OOS) where as in the other table, we have summary. the problem is in the details table, there are 6 OOS where as in summary table, it is showing 9 as a count.
This is the formula used to count OOS. Sum(if(Inventory_Position='OOS',1,0))
Could you help. attaching a word document with details.
this is the back end script:
//NULLASVALUE [Safety Days' Supply];
//NULLASVALUE [Demand Volume (Eaches)];
//NULLASVALUE DFC;
//SET NullValue ='0' ;
WeeklyCorridor:
Left join
LOAD
Week,
[Supplier Loc] & [Customer Loc] & Material as %key,
[Customer Loc] & Material as %key2,
Material,
[Material Description],
[Material Type],
Category,
[Inventory Volume],
[Demand Volume (Eaches)],
DFC,
Brand,
Variant,
Lifecycle,
[Customer Loc],
[Customer Location Description],
[Customer Zone],
[Supplier Loc],
[Supplier Gov BU],
[Corridor Status],
[Min DFC],
[Max DFC],
if([Safety Days Supply]='-',0,[Safety Days Supply]) as [Safety Days Supply],
//Creation of rules
IF(((num([Min DFC])+ num([Max DFC]))<> 0) and num([Min DFC]) >= num([Max DFC]),'Reason- Min >= Max') as Rule1,
IF((num([Safety Days Supply]) + num([Max DFC])) <> 0 and num([Safety Days Supply]) >= num([Max DFC] ), 'Reason-SSD>Max') as Rule2,
IF(((num([Min DFC])+ num([Max DFC]))=0 AND num([Safety Days Supply]) <> 0),'Reason-SSD has value when Min & Max is zero') as Rule3,
If((num([Min DFC]) + num([Max DFC]))=0 and Lifecycle<>'Material Run Down','Reason-Min Max not set') as Rule4,
IF( Lifecycle='Material Run Down' and (num([Min DFC])+ num([Max DFC]))<>0, 'Reason-RunDown SKU Min & Max > Zero') as Rule5,
IF((num([Safety Days Supply])+num([Min DFC]))<>0 and num([Safety Days Supply])<= num([Min DFC]),'Reason-SSD <= Min') as Rule6,
IF([Safety Days Supply]='-' and num([Max DFC])>0, 'Reason-SSD not set') as Rule7,
IF([Safety Days Supply]='-' and (num([Min DFC])+ num([Max DFC]))=0, 'Min & Max = Zero') as Rule8,
if([Min DFC]='-' and [Max DFC]='-','Reason-Min Max not set') as Rule9,
// Inventory Position calcualtions as follows:
if(DFC = 0 and [Inventory Volume] < 0,'OOS',
if(DFC < [Min DFC] and [Inventory Volume] > 0, 'Low Stock',
if(DFC >= [Min DFC] and DFC <= [Max DFC], 'Intolerance',
if(DFC > [Max DFC], 'Overstock',
if(DFC = 0 and [Inventory Volume] = 0, 'NoDemand'))))) as Inventory_Position,
//Creation of action items based on rules created for planners.
IF(((num([Min DFC])+ num([Max DFC]))<> 0) and num([Min DFC]) >= num([Max DFC]),'Reason- Min >= Max',
IF((num([Safety Days Supply]) + num([Max DFC])) <> 0 and num([Safety Days Supply]) >= num([Max DFC] ), 'Reason-SSD>Max',
IF((num([Min DFC])=0 and num([Max DFC])=0 AND num([Safety Days Supply]) > 0),'Reason-SSD has value when Min & Max is zero',
If((num([Min DFC]) + num([Max DFC]))=0 and Lifecycle<>'Material Run Down','Reason-Min Max not set',
IF( Lifecycle='Material Run Down' and (num([Min DFC])+ num([Max DFC]))<>0 , 'Reason-RunDown SKU Min Max > Zero',
IF((num([Safety Days Supply])+num([Min DFC]))<>0 and num([Safety Days Supply])<= num([Min DFC]),'Reason-SSD <= Min',
IF([Safety Days Supply]='-' and num([Max DFC])>0, 'Reason-SSD Notdefined',
IF([Safety Days Supply]='-' and (num([Min DFC])+ num([Max DFC]))=0, 'Min&Max = Zero and SSD is blank',
if([Min DFC]='-' and [Max DFC]='-','Reason-Min Max not set',
if([Min DFC]='-', 'Min Not Set',
if([Max DFC]='-', 'Max Not Set', 'All Set'))))))))))) AS ActionItem
FROM
Load.qvd]
(qvd);
