Max value for Date with Set Analysis

[microwin88x]

Hello,

I have the following table:

- Dimensions: Op_Date / Reference

- Expression: Amount = Sum(Amount)

(Note that Op_Date format is DD/MM/YYYY)

Op_Date	Amount	Reference
13/2/2018	-	-
14/2/2018	200	AB1
14/2/2018	300	AD3
14/2/2018	400	CX5
15/2/2018	250	RT3
15/2/2018	350	TP7
16/2/2018	-	-

What I need is to see the table (modifying my Expression) for the Max Op_Date with Reference <> Null.

So if I select 15/2/2018, I'd see:

Op_Date	Amount	Reference
15/2/2018	250	RT3
15/2/2018	350	TP7

And if I select 16/2/2018, I'd see also (because Reference is Null for that date):

Op_Date	Amount	Reference
15/2/2018	250	RT3
15/2/2018	350	TP7

Do you know how could I do that?

Thank you!!!

[sunny_talwar · Accepted solution]

May be this

Sum({<Op_Date = {"$(=Date(Max({<Op_Date = {[$(='<=' & Date(Max(Op_Date), 'DD/MM/YYYY'))]}, Amount = {

}>}Op_Date), 'DD/MM/YYYY'))"}>}Amount)

[vishsaggi]

Hi Sunny, i have been seeing few of your set analysis expressions you are using [] square brackets does that mean you are doing a search?

[sunny_talwar]

Yes, a search string using [], instead of double quote within double quote

[vishsaggi]

Thanks. Got it. Never used that way.