topic Re: Max value for Date with Set Analysis in QlikView

Max value for Date with Set Analysis

microwin88x — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I have the following table:

- Dimensions: Op_Date / Reference

- Expression: Amount = Sum(Amount)

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

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 — Fri, 06 Jul 2018 19:03:31 GMT

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 — Fri, 06 Jul 2018 19:51:15 GMT

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 — Fri, 06 Jul 2018 19:52:57 GMT

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

vishsaggi — Fri, 06 Jul 2018 19:54:14 GMT

Thanks. Got it. Never used that way.