AGGR without taking into account the current selections

[Anonymous]

The scenario is:

RowID	Year	Month	Date	ID	Filter
1	2017	1	01/01/2017	A	1
2	2017	1	02/01/2017	A	0
3	2017	2	03/01/2017	A	0
4	2017	2	04/02/2017	B	1
5	2017	2	05/02/2017	B	0

I have two list boxes with year and month, when I select both I would like to know the numbers of IDs that have Filter = 1 in their minimum date, and this date has to be <= to the date made with my selections in the lists box makedate(Year,Month,1)).

In this case if I select year 2017 and month Feb I get 0 I guess due to the selections, I would like to retrieve 1 (the RowID=1)

I use the following formula:

=sum(aggr(
if(
date(min({<Year=, Month=>}Date))<= date(makedate(Year,Month,1)),
Count({<Year=, Month=,Filter={'1'}>} distinct  [ID])
)
,RowID,[ID]))

Due to my real scenario I can’t do that through Year and Month as data island or with Alternate States.

Thank you and best regards.

Sara

[sunny_talwar]

May be this

=Sum({<Year, Month>} Aggr(If(Min({<Year, Month>}Date) <= MakeDate(Year, Month,1), Count({<Year, Month, Filter = {'1'}>} DISTINCT [ID])), RowID, [ID]))

[sunny_talwar]

Alternatively, you can try this

Count({<Year, Month, Filter = {'1'}, RowID = {"=Min({<Year, Month>}Date) <= MakeDate(Year, Month,1)"}>} DISTINCT [ID])

[sunny_talwar]

Just tried it out and you might need this

=Count({<Year, Month, Filter = {'1'}, RowID = {"=Min({<Year, Month>}Date) <= Only(TOTAL MakeDate(Year, Month,1))"}>} DISTINCT [ID])

or this

=Sum({<Year, Month>} Aggr(If(Min({<Year, Month>}Date) <= Only(TOTAL MakeDate(Year, Month,1)), Count({<Year, Month, Filter = {'1'}>} DISTINCT [ID])), RowID, [ID]))

[Anonymous]

Thank you It seems that it work:

=Count({<Year, Month, Filter = {'1'}, RowID = {"=Min({<Year, Month>}Date) <= Only(TOTAL MakeDate(Year, Month,1))"}>} DISTINCT [ID])

When I confirm I`ll close and mark as correct.

Best regards.

[Anonymous]

Hi Sunny I was testing with more data and it seems that is doesnt work for example:

RowID	Year	Month	Date	Filter	ID
1	2017	1	01/01/2017	1	A
2	2017	1	02/01/2017	0	A
3	2017	2	03/01/2017	0	A
4	2017	2	04/02/2017	1	B
5	2017	2	05/02/2017	0	B
6	2017	2	06/02/2017	1	C
7	2017	2	07/02/2017	0	C
8	2017	2	08/02/2017	0	C
9	2017	2	09/02/2017	1	D
10	2017	2	10/02/2017	0	D
11	2017	2	11/02/2017	0	D
12	2017	2	12/02/2017	0	D
13	2017	2	13/02/2017	0	D
14	2017	2	14/02/2017	0	E
15	2017	2	15/02/2017	0	E
16	2017	2	16/02/2017	0	E
16	2017	3	17/03/2017	1	E

For example in this table if I selec month =3 and year =2017, I would like to evaluate the min date for each ID and if there is one as filter I would like to count it. In this case the desire result will be 4 RowID(1,4,6 and 9)

Thank you for your advice it is greatly appreciated!

[sunny_talwar]

Try this

=Count({<Year, Month, Filter = {'1'}, ID = {"=Min({<Year, Month, Filter = {'1'}>}Date) <= Only(TOTAL MakeDate(Year, Month,1))"}>} DISTINCT ID)

[Anonymous]

Hi Sunny,

The problem with that is that I dont want to the min date with the filter =1, i would like to evaluate in the min date of each ID if the Filter is equal to 0. Imagine the oposita case i would like to evaluate in the max date:

RowID	Year	Month	Date	Filter	ID
1	2017	1	01/01/2017	1	A
2	2017	1	02/01/2017	0	A
3	2017	2	03/01/2017	0	A
4	2017	2	04/02/2017	1	B
5	2017	2	05/02/2017	0	B
6	2017	2	06/02/2017	1	C
7	2017	2	07/02/2017	0	C
8	2017	2	08/02/2017	0	C
9	2017	2	09/02/2017	1	D
10	2017	2	10/02/2017	0	D
11	2017	2	11/02/2017	0	D
12	2017	2	12/02/2017	0	D
13	2017	2	13/02/2017	0	D
14	2017	2	14/02/2017	0	E
15	2017	2	15/02/2017	0	E
16	2017	2	16/02/2017	0	E
16	2017	3	17/03/2017	1	E

In this case the result will be 0.

Thank you

[sunny_talwar · Accepted solution]

May be like the attached

Have only updated the text boxes and not the tables...

[Anonymous]

Thank you This is what i was looking for