How to sum and filter set analysys without dimension

[mahieuphilippe]

Hi everybody,  i hope to find a help for this question  :

I have  this  :

I want  count of the FNL (dimension) only if by each part number, the total of quantity is greater > 1000 and the status is (4,5,45)

example  for the part number  M114-102-136*T4  i have 1152 pcs so greater than 100,  i count the number of FNL (4) in this case .

if i add the dimension 'Part N°'  to the graph on the left it's working , but if i retry the dimension 'Part N°'  --> no

if i don't select a dimension (Part N°) it's not working 

if i select a specific part N° ,  it's working

my formula  :

if (aggr (sum({<STATUS ={"4","5","45"}>} P1_ACC_QTY),PART_NO) < 1000, null (),

aggr (count({<STATUS ={"4","5","45"}>} FINISHING_LOT),[DATEQVD.autoCalendar.Date],PART_NO))

i think it's not working because qliksense lost the relationship  with  the part N° but it's inside my formula 

thanks for all helps

Ph

[Kushal_Chawda]

@mahieuphilippe  try below

sum(aggr(if(sum({<STATUS={"4","5","45"}>} P1_ACC_QTY)>1000,count(distinct PART_NO)),[DATEQVD.autoCalendar.Date],PART_NO))

[Kushal_Chawda]

@mahieuphilippe  If you have primary key in your data try below

count(distinct {<Primary_Key={"=sum({<STATUS={"4","5","45"}>} P1_ACC_QTY>100"}>} PART_NO)

[mahieuphilippe]

Hi , first thank you very much to respond to my question  🙂 !!!

this is the result of your formula :

this not correct,  with your formula you count how many part n° has more 1000 PCS and it's correct !!  (22)

but for my i want to have the sum of count FNL   so  596 FNL the colums '2'

thanks

[Kushal_Chawda · Accepted solution]

@mahieuphilippe  Yes then you can replace count(Part) with count (FNL)

sum(aggr(if(sum({<STATUS={"4","5","45"}>} P1_ACC_QTY)>1000,count(distinct {<STATUS={"4","5","45"}>} FINISHING_LOT)),[DATEQVD.autoCalendar.Date],PART_NO))

[mahieuphilippe]

i do that and it's work

sum(aggr(if(sum({<STATUS={"4","5","45"}>} P1_ACC_QTY)>1000,count({<STATUS ={"4","5","45"}>} FINISHING_LOT)),[DATEQVD.autoCalendar.Date],PART_NO))  answer 596  great !!!

with your last formula i have 1207 ?

[Kushal_Chawda]

@mahieuphilippe  Yeah. It's just addition of set inside inner aggregation as well.

[mahieuphilippe]

Thank you very much for helping me, it's very nice!!,  i accept as solution !!

Philippe from Belgium