Count by maximum date

[Silvia2]

Hi!

I have a table with the following fields:

Sequence Error_Code Date

Seq1            0                       23/11/2020

Seq1            101                  24/11/2020

Seq2            600                 09/05/2020

Seq2            0                      10/05/2020

Seq3            101                 09/12/2020

Seq3            0                      10/12/2020

I would like to count all the sequences from maximum date which have Error_Code=0

For the example only the Seq2 and Seq3 will be counted

[rubenmarin · Accepted solution]

Hi, this expression may do the work: 

Sum(Aggr(If(Date=Max(TOTAL <Sequence> Date) and Error_Code=0,1),Sequence,Date))

[Silvia2]

Thank you!
And to count the sequences with Error_Code different than 0 how should I have to modify the expression?

Sum(Aggr(If(Date=Max(TOTAL <Sequence> Date) and Error_Code=-0,1),Sequence,Date)) does not work and Sum(Aggr(If(Date=Max(TOTAL <Sequence> Date) and Error_Code>0,1),Sequence,Date)) gives wrong result

[rubenmarin]

Hi, try with: Sum(Aggr(If(Date=Max(TOTAL <Sequence> Date) and Error_Code<>0,1),Sequence,Date))

[Silvia2]

Hi!

Still not working fine

When I make a selection, my values change and I don't understand why

The results are calculated correctly only when i make a selection(2nd photo)

[rubenmarin]

Hi, hard to guess, maybe something with the date model behind. If you could upload a sample that simulates your model with some dummy data to test, I can take a look.