topic How to sum and filter set analysys without dimension in App Development

How to sum and filter set analysys without dimension

mahieuphilippe — Wed, 23 Oct 2024 07:08:11 GMT

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

Re: How to sum and filter set analysys without dimension

Kushal_Chawda — Wed, 23 Oct 2024 11:35:51 GMT

@mahieuphilippe try below

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

Re: How to sum and filter set analysys without dimension

Kushal_Chawda — Wed, 23 Oct 2024 11:38:40 GMT

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

Re: How to sum and filter set analysys without dimension

mahieuphilippe — Wed, 23 Oct 2024 13:12:31 GMT

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

Re: How to sum and filter set analysys without dimension

Kushal_Chawda — Wed, 23 Oct 2024 13:30:17 GMT

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

Re: How to sum and filter set analysys without dimension

mahieuphilippe — Wed, 23 Oct 2024 13:22:41 GMT

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 ?

Re: How to sum and filter set analysys without dimension

Kushal_Chawda — Wed, 23 Oct 2024 13:26:32 GMT

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

Re: How to sum and filter set analysys without dimension

mahieuphilippe — Wed, 23 Oct 2024 13:28:52 GMT

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

Philippe from Belgium