Max Status of Each Account number , year, month wise with set Analysis.

[ankitbisht01]

Hi All,

Just need some help with set analysis. This is my data set.

Account Num	CreatedDateTime	Date	Month	Status
C00001	3/31/2010 2:39:52 PM	03/21/2010	Mar	0
C00001	-	03/31/2010	Mar	0
C00001	-	04/23/2010	Mar	2
C00001	-	04/30/2010	Mar	0
C00001	4/20/2015 8:57:35 AM	04/20/2015	Apr	2
C00001	4/20/2015 11:50:15 AM	04/20/2015	Apr	0
C00001	-	04/30/2015	Apr	0

I want to count only max(Status) of an particular [Account Num] for each Month. where status should be '0'. and there are many Other account numbers as above .

Ex. i only need to count those record which are having status = 0 for the max date of that month . like two of the record which are Bold ,i only need to count those. how can i do it in set analysis.

[sunny_talwar · Accepted solution]

May be like this:

Sum(Aggr(If(FirstSortedValue(Status, -Date) = 0, 1, 0), AccountNum, Month))

[ankitbisht01]

Thank you so much sunny it worked like a charm .

One more help if possible . what script i should write if i want to remove extra rows at back end and only want to keep the max(status) values for each Account number, month and year wise.

[sunny_talwar]

I don't see Year in your data, but assuming it is there, may be this:

Table:

LOAD [Account Num],

          CreatedDateTime,

          Date,

          Month,

          Year,

          Status

FROM ....

Right Join (Table)

LOAD [Account Num],

          Month,

          Year,

          Max(Date) as Date

Resident Table

Group By [Account Num], Month, Year;

[ankitbisht01]

Thank You for help, i will try it out