Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
danielrogra
Creator
Creator

Working with dates - Last date

Hello guys

First of all thanks for your time.

I have a set of data with measures,dimensions and dates. I've created some charts with dates,measures,etc, but I want to show just the data from the last date.

Lets say, I want to show the sum of A over the max(date). I thought: Well, I can convert a date to number an then get the maximum:

max(num(date))

but it seems that it doesn't work properly. When I try to show the max(num(date)) it throws an error. Am I missing something?

Thanks in advance

Regards

1 Solution

Accepted Solutions
cazztfplease
Creator
Creator

Maybe this will work?  Just adding a set analysis statement to the expression to sum 'A' when the date is the last date.

sum({<[Date] = {'$(=max(date([Date])))'}>})

View solution in original post

8 Replies
cazztfplease
Creator
Creator

Maybe this will work?  Just adding a set analysis statement to the expression to sum 'A' when the date is the last date.

sum({<[Date] = {'$(=max(date([Date])))'}>})

MK9885
Master II
Master II

Sum({<Date = {'$(=Max(Date ))'}>} A ))

Maybe above one?

cazztfplease
Creator
Creator

Might work.  Not sure of your date structure.

danielrogra
Creator
Creator
Author

That's great,thank you!!!

I've used that expression and it seems to be working as expected but there is a little issue: I don't want to be affected by any of the filters I have, so I wrote this:

sum({1<[Date] = {'$(=max(date([Date])))'}>})


Unfortunately, if a filter by date, this value is affected by the date I choose. Is there any way to avoid that?


Thanks in advance


Regards

cazztfplease
Creator
Creator

hmmmm... You can try this:


Define a variable (for instance vMaxDate), as:

=max(date([Date]))


Then try:

sum({1<[Date] = {'$(vMaxDate)'}>})



danielrogra
Creator
Creator
Author

Thanks for your time,really.

It's still not working...It's odd because the only filter affect that expression is an string made with year+month, but this string is not made using date, it's made in the script.

I mean

Select date,year(date),month(date),....... from ##blablabla

What do you think?

Thanks for your time

MK9885
Master II
Master II

Ignore selection in an expression (Set Analysis)

Set analysis - ignore the selection?

Check out above 2 links.

1 is ignoring all selections and $ is used to ignore what is required.

danielrogra
Creator
Creator
Author

Hello Aehman.

Thank your for those links!

The problem is that I'm using 1, so I expected that my formula would ignore all selections, but it's not working...

Any clue?

Thanks!