Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in NYC Sept 4th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
mahieuphilippe
Creator
Creator

How to sum and filter set analysys without dimension

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 .

 

mahieuphilippe_4-1729667098199.png

 

 

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 

 

mahieuphilippe_2-1729666739420.png

 

 

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

 

 

mahieuphilippe_3-1729666815046.png

 

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

 

 

Labels (1)
1 Solution

Accepted Solutions
Kushal_Chawda

@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))

 

View solution in original post

7 Replies
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
Creator
Creator
Author

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

 

this is the result of your formula :

 

mahieuphilippe_0-1729688672678.png

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

mahieuphilippe_1-1729689133896.png

 

 

 

Kushal_Chawda

@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
Creator
Creator
Author

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 ?

 

 

mahieuphilippe_0-1729689785185.png

 

Kushal_Chawda

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

mahieuphilippe
Creator
Creator
Author

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