How count negative differences

[irmantas]

Hi all,

I don't know how write expression to find count negatives for 'Available'-'Consumption' per each Item and total for ProdId

Prod ID	ItemID	Consumption	Available
PROD1	ItemA	5	3
PROD1	ItemB	12	13
PROD1	ItemC	3	3
PROD1	ItemD	2	1
PROD2	ItemA	3	3
PROD2	ItemB	5	13
PROD2	ItemC	3	3
PROD2	ItemD	2	1
PROD2	ItemE	10	9

I am looking to find:

PROD1 | 2   (3-5=-2 and 1-2=-1)

PROD2 | 2  (1-2=-1 and 9-10=-1)

[stevejoyce]

Is it fine to flag in load script/data model?

loadscript:

source_data:
Load
*,
if(Available<Consumption, 1,0) as Fl_NotEnoughAvailable;
load
*

from https://community.qlik.com/t5/App-Development/How-count-negative-differences/m-p/1858783#M70890
(html, utf8, embedded labels, table is @1)
;

table calc:

count({<Fl_NotEnoughAvailable = {1}>} ItemID)

[stevejoyce]

The equivalent to do only in front-end would be like...

sum(aggr(if(Available<Consumption,1),[Prod ID], ItemID))

[MarcoWedel]

-Sum(Consumption>Available)

hope this helps

Marco

[irmantas]

Thank you Stevejoyce, but I can't do it on loading, because Availability I'm  calculating from other tables.

Your equivalent for front-end is not correct and showing "0" at all  cases. Do you have any other solution?