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.

1 Solution

Accepted Solutions
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

View solution in original post

15 Replies
kouroshkarimi
Creator III
Creator III

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

Not applicable
Author

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...).

Not applicable
Author

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

kouroshkarimi
Creator III
Creator III

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

Not applicable
Author

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

Not applicable
Author

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.

MayilVahanan

HI

     Try like this,

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

Hope it helps

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
Not applicable
Author

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.

MayilVahanan

Hi

     Did you get output?

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.