Count/Sum Based on Distinct ID, Min Date and Other Parameters

fietzn — Wed, 28 Oct 2020 18:24:08 GMT

Hi everyone,

I'm a new Qlik user and am trying to perform a couple calculations with my data that I've been unable to solve for by reviewing other threads/posts. I've included a sample of my data below.

Count distinct cases by each month based on Close Date where:
1. Stage = 'Closed' or 'Archived'
2. and Status = 'Active'
3. and Action <> 'Cancel Case'
4. and Close Date is the min/first date for the particular case
  1. I don't want to count a case multiple times if it was closed multiple times
Sum the $ amount by each month based on Closed Date for each distinct case based on the same parameters above

I think it may be a combination of using the Aggr function and set analysis, but I haven't figured out how to combine them properly to get it to work.

Thanks.

Case	Stage	Status	Close Date	ReOpen Date	Action	$
0001	Archived	Active	10/1/2020	10/14/2020	Close Case	1000
0001	Archived	Active	10/28/2020	10/14/2020	Close Case	1000
0001	Archived	Deleted	10/1/2020	10/14/2020	Close Case	1000
0001	Archived	Deleted	10/28/2020	10/14/2020	Close Case	1000
0002	Closed	Active	9/30/2020	-	Close Case	500
0003	Open	Active	-	-	Initiated	200
0004	Closed	Active	8/8/2020	-	Cancel Case	300
0005	Closed	Active	8/15/2020	9/1/2020	Close Case	650
0005	Closed	Deleted	9/11/2020	9/1/2020	Close Case	650

Re: Count/Sum Based on Distinct ID, Min Date and Other Parameters

Anil_Babu_Samineni — Wed, 28 Oct 2020 18:47:50 GMT

@fietzn wrote: Perhaps this

Count distinct cases by each month based on Close Date where:
Stage = 'Closed' or 'Archived'
and Status = 'Active'
and Action <> 'Cancel Case'
and Close Date is the min/first date for the particular case
I don't want to count a case multiple times if it was closed multiple time
Count( DISTINCT {<[Close Date={"$(=Min({<[Close Date]={"=Count(TOTAL <Case> [Close Date])>1"}>} TOTAL <Case> [Close Date]))"}]>} If(Stage = 'Closed' or Status='Archived') and Status = 'Active' and Action<>'Cancel Case' , [Close Date]))
2. Sum the $ amount by each month based on Closed Date for each distinct case based on the same parameters above
Sum(Aggr(Sum( DISTINCT {<[Close Date={"$(=Min({<[Close Date]={"=Count(TOTAL <Case> [Close Date])>1"}>} TOTAL <Case> [Close Date]))"}]>} If(Stage = 'Closed' or Status='Archived') and Status = 'Active' and Action<>'Cancel Case' , Amount)),[Close Date]))

topic Re: Count/Sum Based on Distinct ID, Min Date and Other Parameters in App Development

Count/Sum Based on Distinct ID, Min Date and Other Parameters

Re: Count/Sum Based on Distinct ID, Min Date and Other Parameters