topic Re: How to do less than or equal to Date in Set Analysis? in QlikView

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

Mon, 13 Aug 2012 09:56:50 GMT

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.

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

kouroshkarimi — Mon, 13 Aug 2012 10:22:57 GMT

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?

Mon, 13 Aug 2012 10:24:21 GMT

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?

Mon, 13 Aug 2012 10:31:25 GMT

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?

kouroshkarimi — Mon, 13 Aug 2012 10:42:11 GMT

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?

Mon, 13 Aug 2012 10:43:32 GMT

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?

Mon, 13 Aug 2012 18:36:43 GMT

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?

MayilVahanan — Mon, 13 Aug 2012 19:14:35 GMT

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?

Mon, 13 Aug 2012 19:45:38 GMT

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?

MayilVahanan — Mon, 13 Aug 2012 21:38:30 GMT

Did you get output?

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

Mon, 13 Aug 2012 22:35:23 GMT

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?

Sokkorn — Tue, 14 Aug 2012 01:14:52 GMT

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?

Tue, 14 Aug 2012 01:54:45 GMT

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?

Tue, 14 Aug 2012 02:15:25 GMT

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

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

Sokkorn — Tue, 14 Aug 2012 02:22:15 GMT

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

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

Tue, 14 Aug 2012 02:37:54 GMT

Thanks Sokkorn, nice work!