Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
bbastro1106
Contributor III
Contributor III

cannot get the correct count

I have a table like below.

Fields Total Quantity expression : Sum({$<Quantity = - {'0'}>}Quantity)

  • The above expression is meant to only sum when value under Quantity is not equal to 0

Field Original Quantity : Sum([Original Quanity])

Field Short Ship QTY expression : Sum({$<Quantity = - {'0'}>} Quantity-[Original Quanity])

  • The above expression is meant to get the number of quantity - original quantity, however it should not perform the calculation if quantity = 0

Field Short Ship PO COUNT expression: 

if(
( Sum({$<Quantity = - {'0'}>}Quantity) ) -
( Sum({$<Quantity = - {'0'}>}[Original Quanity]) ) < 0 ,
(Count (distinct [PO Number])),0)

  • The above expression is to perform PO distinct count if the short ship qty  is < 0

With the above expression, i am able to get result like below table

Vendor NameTotal QuantityOriginal QuantityShort Ship QTYShort Ship PO CountPO Number
ABC Vendor06,3120010001
ABC Vendor3,7973,798-1110002
ABC Vendor02,5020010003
ABC Vendor01,4880010004
ABC Vendor1,2991,302-3110005
ABC Vendor01,0020010006
ABC Vendor9909900010007
ABC Vendor09000010008
ABC Vendor7807800010009
ABC Vendor06480010010
ABC Vendor06000010011
ABC Vendor05520010012
ABC Vendor3663660010013
ABC Vendor3363360010014
ABC Vendor3363360010015
ABC Vendor329330-1110016
ABC Vendor3243240010017
ABC Vendor3003000010018
ABC Vendor2472461010019
ABC Vendor02160010020
ABC Vendor1681680010021
ABC Vendor1621620010022
ABC Vendor1621620010023
ABC Vendor1561560010024
ABC Vendor1381380010025
ABC Vendor1321320010026
ABC Vendor1081080010027
ABC Vendor1081080010028
ABC Vendor1081080010029
ABC Vendor84840010030
ABC Vendor72720010031
ABC Vendor60600010032
ABC Vendor0600010033
ABC Vendor54540010034
ABC Vendor48480010035
ABC Vendor36360010036
ABC Vendor12120010037

 

If looks correct, but if i pull out the PO number from the table, the Short Ship PO Count will show me 37 which is counting all the PO but not 3.

Can you please help me to figure it out where is the problem from?

PS. I am not allow to create variable in my APPS.

1 Solution

Accepted Solutions
bbastro1106
Contributor III
Contributor III
Author

Thanks Channa for helping me out.

With below expression is now work! Thanks!

Count(Distinct {<[PO Number]={"=Sum({$<Quantity = - {0}>} Quantity-[Original Quanity])<0"}>} [PO Number])

View solution in original post

5 Replies
Channa
Specialist III
Specialist III

those are aggregate function they will show only 3 if you remove PONUmber

 

Sum({$<Quantity = - {'0'}>}Quantity)

 

Sum({$<Quantity = - {0}>}Quantity)

you dont need single codes it is numlber

 

Channa
bbastro1106
Contributor III
Contributor III
Author

Thanks Channa.

I was thinking to use the AGGR function but I am not quite sure how i can apply aggr to below formula due to the complexity. 

if(AGGR
( Sum({$<Quantity = - {0}>}Quantity) ) -
( Sum({$<Quantity = - {0}>}[Original Quanity]) ) < 0 ,
(Count (distinct [PO Number])),0)

 

If anyone can help and guide me how i can apply AGGR in this case?

bbastro1106
Contributor III
Contributor III
Author

I really need some help on this as i am not sure how i can apply AGGR function with my case.

 

bbastro1106
Contributor III
Contributor III
Author

I try something like below but receive error is expression.

SUM( Aggr
count (
if(
(( Sum({$<Quantity = - {0}>}Quantity) ) - ( Sum({$<Quantity = - {0}>}[Original Quanity]) ) )< 0 ,1,0),

distinct [PO Number])
)

bbastro1106
Contributor III
Contributor III
Author

Thanks Channa for helping me out.

With below expression is now work! Thanks!

Count(Distinct {<[PO Number]={"=Sum({$<Quantity = - {0}>} Quantity-[Original Quanity])<0"}>} [PO Number])