Working with dates - Last date

[danielrogra]

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

[cazztfplease · Accepted solution]

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]

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

Maybe above one?

[cazztfplease]

Might work.  Not sure of your date structure.

[danielrogra]

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]

hmmmm... You can try this:

Define a variable (for instance vMaxDate), as:

=max(date([Date]))

Then try:

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

[danielrogra]

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]

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]

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!