How to do less than or equal to Date in Set Analysis?

[#1]

Hi,

How to do this in set analysis,

I want to display data where DATE is lessthan or equal to the maximum date

Say, I selected or maximum date is 8/1/2012, so all data up to 8/1/2012 will be displayed. See my example below, it's not working!

sum({$<[Days Over] = {'<=0'}, [Document Date] = {'<=max([Document Date])'}>} [Total AR])

Please help. Thanks.

[#2]

Hi Mayil,

I can't still get the output. Please need help.

I want to have output like below:

I the maximum Document Date selected is 8/12/2012

Then this should be the following dates shown on the report

Document Date     Amount

1/20/2010               5,000

12/3/2011               3,000

1/7/2012                 2,000

8/12/2012               8,000

And If the next maximum Document Date selected is 12/3/2011

Then this should be the result

Document Date     Amount

1/20/2010               5,000

12/3/2011               3,000

[Sokkorn]

Hi,

It set analysis should be work for you.

=SUM({$<[Days Over] = {"<=0"}, [Document Date] = {"<=$(=DATE(max([Document Date])))"}>} [Total AR])

Regards,

Sokkorn

[#4]

Hi Sokkorn, not worked, can u give me sample please? Because does not really work. Thanks.

[#5]

Thanks for your help guys,

I just did it this way,

I created a variable name maxDate = max(Document Date)

[Document Date] = {"<=$(maxDate)"}

I guess, you were all CORRECT! It only my mistake. Thanks, til next time.

-Bill

[Sokkorn · Accepted solution]

Hi,

1. Load data

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/DD/YYYY';
SET TimestampFormat='M/DD/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
[Data]:
LOAD * INLINE [
Document Date,     Amount
1/20/2010,         5000
12/3/2011,         3000
1/7/2012,          2000
8/12/2012,         8000];

** Make sure you set your DateFormat = 'M/DD/YYYY'

2. Create Straight Table with

     a. [Document Date] as Dimension

     b. SUM({$<[Document Date] = {"<=$(=DATE(max([Document Date])))"}>} Amount)     as Expression

See sample attached file.

Regards,

Sokkorn

[#7]

Thanks Sokkorn, nice work!