Sum if , agg function and distinct

bushpalaamarnat — Tue, 15 May 2018 12:17:07 GMT

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]) )

Re: Sum if , agg function and distinct

sunny_talwar — Tue, 15 May 2018 12:19:12 GMT

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

Re: Sum if , agg function and distinct

bushpalaamarnat — Tue, 15 May 2018 12:43:08 GMT

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

Re: Sum if , agg function and distinct

sunny_talwar — Tue, 15 May 2018 12:53:06 GMT

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]) )

Re: Sum if , agg function and distinct

bushpalaamarnat — Tue, 15 May 2018 14:17:11 GMT

Thank you!

topic Re: Sum if , agg function and distinct in QlikView

Sum if , agg function and distinct

Re: Sum if , agg function and distinct

Re: Sum if , agg function and distinct

Re: Sum if , agg function and distinct

Re: Sum if , agg function and distinct