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: 
Not applicable

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

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.

15 Replies
Not applicable
Author

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
Master
Master

Hi,

It set analysis should be work for you.

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

Regards,

Sokkorn

Not applicable
Author

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

Not applicable
Author

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
Master
Master

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

Untitled.jpg

See sample attached file.

Regards,

Sokkorn

Not applicable
Author

Thanks Sokkorn, nice work!