Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

agnie_john
New Contributor II

[Count negative Shortage]

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.

3 Replies

Re: [Count negative Shortage]

May be this

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

Employee
Employee

Re: [Count negative Shortage]

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
New Contributor II

Re: [Count negative Shortage]

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.

Community Browser