AutoCalendar not working properly with if-statements & set-analysis

[bartdeboed]

Hi all,

I'm having some trouble with the AutoCalendar function when using it within if-statements and set-analysis. While searching this issue, I've found a post from 2015 that indicated it was a bug. However, I can't find whether this bug has been resolved or not. The post has been 'solved', but the solution just says that it is reported as a bug.

What happens is that if I use an AutoCalendar field within a set-analysis expression OR in an if-statement, it doesn't work.

For example: Count({<[Datefield.autoCalendar.Year]={">2016"} >} ID) does not work, while if I load year(Datefield) as Yearfield, then the following does work; Count({<Yearfield={">2016"} >} ID)

Another example is when I make a linechart and add the dimension: if([Datefield.autoCalendar.Year]>2016,Datefield.autoCalendar.Year), it does not work. While, If(year(Datefield)>2016,Datefield.autoCalendar.Year) does work.

This is the script I use in the load script for AutoCalendar:
[autoCalendar]:
DECLARE FIELD DEFINITION Tagged ('$date')
FIELDS
Dual(Year($1), YearStart($1)) AS [Year] Tagged ('$axis', '$year')

Can anyone tell me if I'm doing it wrong or whether it is still a bug?

[marcus_sommer]

This: Dual(Year($1), YearStart($1)) AS [Year] isn't a year else a (formatted) timestamp because yearstart() returned a timestamp - and this is not a bug. Therefore just use year(Datefield) if you want to have a year-field.

- Marcus

[Vegar · Accepted solution]

As @marcus_sommer is saying, your year field is holding a dual value where the numeric part is a date and the text part is the four digit year. This is similar to how the YearName() function works.

Applying set analysis on dual values can be a bit tricky because you need to know if you are to consider the numeric or string value in your set modifier. 

To do a comparing  set analysis on such a field you need to compare with the date value not the four digit year. (Assuming you date format is YYYY-MM-DD) 

Count({<[Datefield.autoCalendar.Year]={">'2016-01-01' "} >} ID)

Observe that the expression below will also work as you are not evaluating the numeric but comparing the string, but here you get the count for a single year. 

Count({<[Datefield.autoCalendar.Year]={'2016'} >} ID) 

[bartdeboed]

Thank you for the elaborate answer, I understand the issue perfectly now. What I don't understand, is why Qlik would use this dual function in their autocalendar script, as the one I used was generated by the data manager. Very confusing...

[Vegar]

It guess it is a  matter of taste,  I personally tend not to use duals() in Year fields, but I do use dual values when working on  year-month or year-week fields, often generated by monthname(DateKey) or weekname(), and then the set modifiers have fit those fields in the same manner as you Year field. I have however found the dual in year to be handy in some scenarious when working with both calendar year and fiscal year-calendars. 

[bartdeboed]

Hi Vegar,

If you remove the dual, it is no longer possible to have a continuous scale automatically. If I change the x-axis to manual and select 'continuous', the years turn into 2,01k and 2,02k.

The continuous scale is something we value a lot. Is there any way to fix this while maintaining the possibility to use set-analysis as described above (year instead of date)?

[bismart]

why not create a second Year field called YearSA for example, without the dual ?

This will allow you use one field for continuous scale and the other for Set analysis