Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
microwin88x
Creator III
Creator III

Max value for Date with Set Analysis

Hello,

I have the following table:

- Dimensions: Op_Date / Reference

- Expression: Amount = Sum(Amount)

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

Op_DateAmountReference
13/2/2018--
14/2/2018200AB1
14/2/2018300AD3
14/2/2018400CX5
15/2/2018250RT3
15/2/2018350TP7
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_DateAmountReference
15/2/2018250RT3
15/2/2018350TP7

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

Op_DateAmountReference
15/2/2018250RT3
15/2/2018350TP7

Do you know how could I do that?

Thank you!!!

1 Solution

Accepted Solutions
sunny_talwar

May be this

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

  • }>}Op_Date), 'DD/MM/YYYY'))"}>}Amount)
  • View solution in original post

    4 Replies
    sunny_talwar

    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
    Champion III
    Champion III

    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
    Champion III
    Champion III

    Thanks. Got it. Never used that way.