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

[fietzn]

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. 

1. 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
2. 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

[Anil_Babu_Samineni · Accepted solution]

---

@fietzn wrote: Perhaps this 

1. 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 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]))

---