Problems with Auto Calendar in Set Analysis

[Anonymous]

Hello,

I'm in trouble with Auto Calendar.

I need to filter data smaller than a specific year and it's not working, please see below:

AutoCalendar:

SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='R$#.##0,00;-R$#.##0,00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';
SET FirstWeekDay=6;
SET BrokenWeeks=1;
SET ReferenceDay=0;
SET FirstMonthOfYear=1;
SET CollationLocale='pt-BR';
SET CreateSearchIndexOnReload=1;
SET MonthNames='jan;fev;mar;abr;mai;jun;jul;ago;set;out;nov;dez';
SET LongMonthNames='janeiro;fevereiro;março;abril;maio;junho;julho;agosto;setembro;outubro;novembro;dezembro';
SET DayNames='seg;ter;qua;qui;sex;sáb;dom';
SET LongDayNames='segunda-feira;terça-feira;quarta-feira;quinta-feira;sexta-feira;sábado;domingo';
SET NumericalAbbreviation='3:k;6:M;9:G;12:T;15:P;18:E;21:Z;24:Y;-3:m;-6:μ;-9:n;-12:p;-15:f;-18:a;-21:z;-24:y';


[autoCalendar]: 
  DECLARE FIELD DEFINITION Tagged ('$date')
FIELDS
  Dual(Year($1), YearStart($1)) AS [Year] Tagged ('$axis', '$year'),
  Dual('Q'&Num(Ceil(Num(Month($1))/3)),Num(Ceil(NUM(Month($1))/3),00)) AS [Quarter] Tagged ('$quarter', '$cyclic'),
  Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [YearQuarter] Tagged ('$yearquarter', '$qualified'),
  Dual('Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [_YearQuarter] Tagged ('$yearquarter', '$hidden', '$simplified'),
  Month($1) AS [Month] Tagged ('$month', '$cyclic'),
  Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth', '$qualified'),
  Dual(Month($1), monthstart($1)) AS [_YearMonth] Tagged ('$axis', '$yearmonth', '$simplified', '$hidden'),
  Dual('W'&Num(Week($1),00), Num(Week($1),00)) AS [Week] Tagged ('$weeknumber', '$cyclic'),
  Date(Floor($1)) AS [Date] Tagged ('$axis', '$date', '$qualified'),
  Date(Floor($1), 'D') AS [_Date] Tagged ('$axis', '$date', '$hidden', '$simplified'),
  If (DayNumberOfYear($1) <= DayNumberOfYear(Today()), 1, 0) AS [InYTD] ,
  Year(Today())-Year($1) AS [YearsAgo] ,
  If (DayNumberOfQuarter($1) <= DayNumberOfQuarter(Today()),1,0) AS [InQTD] ,
  4*Year(Today())+Ceil(Month(Today())/3)-4*Year($1)-Ceil(Month($1)/3) AS [QuartersAgo] ,
  Ceil(Month(Today())/3)-Ceil(Month($1)/3) AS [QuarterRelNo] ,
  If(Day($1)<=Day(Today()),1,0) AS [InMTD] ,
  12*Year(Today())+Month(Today())-12*Year($1)-Month($1) AS [MonthsAgo] ,
  Month(Today())-Month($1) AS [MonthRelNo] ,
  If(WeekDay($1)<=WeekDay(Today()),1,0) AS [InWTD] ,
  (WeekStart(Today())-WeekStart($1))/7 AS [WeeksAgo] ,
  Week(Today())-Week($1) AS [WeekRelNo] ;

Data:

Load *

Inline 
[
ID,NAME,DATE1
1,Jhon,25/01/2011
2,Mark,25/01/2012
3,James,25/01/2013
4,Steve,25/01/2014
5,Donald,25/01/2015
6,Kevin,25/01/2016
7,Bob,25/01/2017
8,George,25/01/2018
9,Frank,25/01/2019
10,Larry,25/01/2020
];

DERIVE FIELDS FROM FIELDS [DATE1] USING [autoCalendar] ;

Set Analysis 

// Works:
Count({$<[DATE1.autoCalendar.Year] = {'2017'} >} ID)

// Not work:
Count({$<[DATE1.autoCalendar.Year] = {'<2017'} >} ID)

// Not work
Count({$<[DATE1.autoCalendar.Year] = {'>2017'} >} ID)

Someone can help me ?

Thanks in advance.

MRBranco

[juliensaintmaxent3113]

Hi,

try : Count({$<[DATE1.autoCalendar.Year] = {"<2017"} >} ID) with double quote and not sample quote.

if i'm not mistaken, the sample quote is use for a value as text and the double quote for a value for Date or numeric or fields...

Bye

[sunny_talwar · Accepted solution]

Can you try these and see if these work

Count({$<[DATE1.autoCalendar.Year] = {"<$(=YearStart(MakeDate(2017)))"}>} ID)
Count({$<[DATE1.autoCalendar.Year] = {">$(=YearStart(MakeDate(2017)))"}>} ID)

[marcus_malinow]

---

@juliensaintmaxent3113 wrote:

the sample quote is use for a value as text and the double quote for a value for Date or numeric or fields...

---

Your syntax is correct, but the reasoning is slightly off.

Single quote is when you explicitly state the value, double quote is where you want to use a search string. This is applicable for text, numeric, or date.

[juliensaintmaxent3113]

Indeed, thanks for yours extar informations

[Anonymous]

@sunny_talwar

Count({$<[DATE1.autoCalendar.Year] = {"<$(=YearStart(MakeDate(2017)))"}>} ID)

It worked!

Thank you !

I have just one more question.

How can I get Null dates?

I've tried the code below:

Count({$<[DATE1.autoCalendar.Year] = {"=$(=YearStart(MakeDate('')))"}>} ID)

But It didn't work.

Data:

Thank you again.

MRBranco.

[juliensaintmaxent3113]

Hello,

it is rather advisable to treat the null value the script by putting a value like 01-01-3000 or another.

After, you can use the same expresion with this value.

Bye 

[sunny_talwar · Accepted solution]

Try this

Count({$<[DATE1.autoCalendar.Year] = {"<$(=YearStart(MakeDate(2017)))"}>+
        <ID = {"=Len(Trim(DATE1)) = 0"}>} ID)

[Anonymous]

It worked !

Thank you !