Skip to main content
Announcements
Accelerate Your Success: Fuel your data and AI journey with the right services, delivered by our experts. Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
irmantas
Contributor III
Contributor III

How count negative differences

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)

Labels (1)
4 Replies
stevejoyce
Specialist II
Specialist II

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
Specialist II
Specialist II

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
Contributor III
Contributor III
Author

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?