Sum if , agg function and distinct

[bushpalaamarnat]

Hi, Could any one help.

I tried different combination to get volume only by following syntax in expression and getting only out put as zero.

I need inventory volume  if  N_Inventory_Position = Overstock. By now you would got what we are trying to derive the over stock inventory volume.

//sum({<N_Inventory_Position = {'Overstock'}>} Aggr(Sum(DISTINCT {<N_Inventory_Position = {'Overstock'} [Inventory Volume]), Week&Material&[Supplier Loc]&[Customer Loc]) )

sum({<N_Inventory_Position = {'Overstock'}>} Aggr(Sum(DISTINCT {<N_Inventory_Position = {'Overstock'} [Inventory Volume]), Week,Material,[Supplier Loc],[Customer Loc]) )

[sunny_talwar]

Would you be able to share a sample to check this out?

[bushpalaamarnat]

Hello Sunny,

Thank you for response, i prepared a dummy data of 6 rows for now attached in the reply.

Following the script.

WeeklyCorridor:

Left join

LOAD

Week,

[Supplier Loc] & [Customer Loc] &  Material as %key,

[Customer Loc] &  Material as %key2,

    Material,

    [Material Description],

    [Material Type],

    Category,

if([Inventory Volume]='-',0,[Inventory Volume]) as [Inventory Volume],

    if([Demand Volume (Eaches)]='-',0,[Demand Volume (Eaches)]) as [Demand Volume (Eaches)],

    if(DFC='-',0,DFC) as 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

[sunny_talwar · Accepted solution]

You are missing a closing >}

sum({<N_Inventory_Position = {'Overstock'}>} Aggr(Sum(DISTINCT {<N_Inventory_Position = {'Overstock'}>} [Inventory Volume]), Week,Material,[Supplier Loc],[Customer Loc]) )

[bushpalaamarnat]

Thank you!