Skip to main content

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
bartdeboed
Partner - Contributor III
Partner - Contributor III

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

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?

Labels (2)
1 Solution

Accepted Solutions
Vegar
MVP
MVP

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) 

View solution in original post

6 Replies
marcus_sommer
MVP & Luminary
MVP & Luminary

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
MVP
MVP

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
Partner - Contributor III
Partner - Contributor III
Author

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
MVP
MVP

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
Partner - Contributor III
Partner - Contributor III
Author

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
Creator
Creator

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