[Count negative Shortage]

[agnie_john]

Hi,

I am having  a requirement of finding number of items which have the shortage. sum (inventory) - sum(requirement of current month).

The following is the formula to find the the shortage

$(Inventory) + $(ParentInventory)-(sum({<ReqDate={"<=$(=MonthEnd(Today()))"}>}req*Usage)) .

I want the count of items which have -ve  values(Shortage).

I thought of using aggr function,but i couldn't get the my result.

Could you please provide me a solution?

Thanks,

John.

[sunny_talwar]

May be this

Count(DISTINCT {<Item = {"=$(Inventory) + $(ParentInventory)-(sum({<ReqDate={"<=$(=MonthEnd(Today()))"}>}req*Usage))"}>} Item)

[Greg_Williams]

What is ReqDate format? 04-02-2018 or 4-2-2018 or 40318 or 4-2-2018 13:12 and what is being returned by MonthEnd?

-gw

[agnie_john]

Thanks for your response Sunny Talwar.

I have solved my issue.

I create variable

Nth Shortage=

$(Inventory)+$(G60Inventory)-$(nthMonthRequirement)

and

sum(if(Aggr($(Nth Shortage),Item)<0,1,0))

The above expression return negative count.