
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Problems with Auto Calendar in Set Analysis
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
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try this
Count({$<[DATE1.autoCalendar.Year] = {"<$(=YearStart(MakeDate(2017)))"}>+
<ID = {"=Len(Trim(DATE1)) = 0"}>} ID)
.png)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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.
.png)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Indeed, thanks for yours extar informations

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
.png)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try this
Count({$<[DATE1.autoCalendar.Year] = {"<$(=YearStart(MakeDate(2017)))"}>+
<ID = {"=Len(Trim(DATE1)) = 0"}>} ID)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you !
