15 Replies Latest reply: Aug 13, 2012 10:37 PM by Bill Ringer Salalima

# 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])

• ###### Re: How to do less than or equal to Date in Set Analysis?

You need to create using search strings, e.g: Sum({\$<[Days Over]={"<=\$(=0)"},[Document Date]={"<=\$(=max([Document Date]))"}>}[Total AR])

• ###### Re: How to do less than or equal to Date in Set Analysis?

I think this should do it.

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

Hope it helps.

Note that the maximum is calculated only 1 time (which to me seems no problem here...).

• ###### Re: How to do less than or equal to Date in Set Analysis?

Guys it only displays document date EQUAL to maximum date! It does displays less than maximum dates.

• ###### Re: How to do less than or equal to Date in Set Analysis?

maybe just wrap it in an If statement and remove the max date from the set analysis? if([Document Date]<=max([Document Date]], Sum({\$<[Days Over]={"<=\$(=0)"}>}[Total AR]))

• ###### Re: How to do less than or equal to Date in Set Analysis?

Please find the attached file. I used the fieldnames that you use...

In this example you can see that the given formula results in the sum of all days <= 3-1-2012 and not only the sum of 3-1-2012. So the syntax seems to be correct...?

If you agree that it works in my sample here... You can check where your problem lies.

I often mouseover the expression label to see what is calculated in the setAnalysis. When no label is given to an expression, you can see the outcome of {"<=\$(=max([Document Date]))"} by mousing over the column label and see the expression that Qlikview uses...

Here I see that the date <=40911 which is <=3-1-2012 in my example

Hope it helps

• ###### Re: How to do less than or equal to Date in Set Analysis?

It should be like this,

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

If possible, I want to use SET ANALYSIS for this, if not working, please let me know how to solve this. Thanks in advance.

• ###### Re: How to do less than or equal to Date in Set Analysis?

HI

Try like this,

=Sum({<DocumentDate = {'>=\$(=MonthStart(Min({1}DocumentDate)))<=\$(=MonthEnd(Max(DocumentDate)))'}>}Amount)

Hope it helps

• ###### Re: How to do less than or equal to Date in Set Analysis?

It's now working. It's the same output, it only displays date base on selection.

It should be that, all dates will displayed up to the maximum selected date.

Say, if I choose date 5/5/2012, all dates less than or equal to 5/5/2012 will be displayed.

• ###### Re: How to do less than or equal to Date in Set Analysis?

Hi

Did you get output?

• ###### Re: How to do less than or equal to Date in Set Analysis?

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

• ###### Re: How to do less than or equal to Date in Set Analysis?

Hi,

It set analysis should be work for you.

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

Regards,

Sokkorn

• ###### Re: How to do less than or equal to Date in Set Analysis?

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

• ###### Re: How to do less than or equal to Date in Set Analysis?

Hi,

```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]:
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

• ###### Re: How to do less than or equal to Date in Set Analysis?

Thanks Sokkorn, nice work!

• ###### Re: How to do less than or equal to Date in Set Analysis?

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