Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
You need to create using search strings, e.g: Sum({$<[Days Over]={"<=$(=0)"},[Document Date]={"<=$(=max([Document Date]))"}>}[Total AR])
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...).
Guys it only displays document date EQUAL to maximum date! It does displays less than maximum dates.
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]))
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
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.
HI
Try like this,
=Sum({<DocumentDate = {'>=$(=MonthStart(Min({1}DocumentDate)))<=$(=MonthEnd(Max(DocumentDate)))'}>}Amount)
Hope it helps
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.
Hi
Did you get output?