Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

danielrogra
New Contributor III

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

Tags (2)
1 Solution

Accepted Solutions
cazztfplease
Contributor

Re: Working with dates - Last date

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])))'}>})

8 Replies
cazztfplease
Contributor

Re: Working with dates - Last date

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])))'}>})

Khan_Mohammed
Honored Contributor II

Re: Working with dates - Last date

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

Maybe above one?

cazztfplease
Contributor

Re: Working with dates - Last date

Might work.  Not sure of your date structure.

danielrogra
New Contributor III

Re: Working with dates - Last date

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
Contributor

Re: Working with dates - Last date

hmmmm... You can try this:


Define a variable (for instance vMaxDate), as:

=max(date([Date]))


Then try:

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



danielrogra
New Contributor III

Re: Working with dates - Last date

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

Khan_Mohammed
Honored Contributor II

Re: Working with dates - Last date

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
New Contributor III

Re: Working with dates - Last date

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!

Community Browser