Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
valmanar
Contributor III
Contributor III

Set analysis with autoCalendar

Hi, I have a autoCalendar was automatically generated from a date field in my fact table "fecha_inicial", I created a filter panel with the following statement:

[fecha_inicial.autoCalendar.Year], in the selected one year and I want in a Kpi show me the sales of the year prior to the selected in the filter panel. For that:

Sum ({$ <[fecha_inicial.autoCalendar.Year = {$ (= Max ([fecha_inicial.autoCalendar.Year]) - 1)}>} sales)

This statement does not work, however if I replace the field generated in autoCalendar "fecha_inicial" with an integer database field that only contains the year: 2017, 2016 etc, it works correctly, the problem comes from using fecha_inicial.autoCalendar.Year, which can be the reason?.

The autoCalendar line that was generated automatically is:.

[autoCalendar]

DECLARE FIELD DEFINITION Tagged ('$ date')

FIELDS

Dual (Year ($1), YearStart ($1)) AS [Year] Tagged ('$ axis', '$ year')

Tested on the latest version: Jun 2017

Thanks and regards.

17 Replies
sunny_talwar

It might be still be a bug

Derived fields not working in Set Analysis

sunny_talwar

Also, you might find this article to be interesting

Q-Tip #16 – What’s Special About Those Derived Fields | Natural Synergies

Anonymous
Not applicable

I suspect that the problem comes from the dual() - not sure why would you need it.

The max() function in your case most likely returns the year start date, and -1 moves it back one day, to the end of the previous year, not to the whole previous year.

Test in a text box what this return:

= Max ([fecha_inicial.autoCalendar.Year]) - 1

If it is not what you want, get rid of dual().  If you need this dual for some reason I don't know, change the above to

= Max (year([fecha_inicial.autoCalendar.Year])) - 1

Anonymous
Not applicable

Try

({$ <[fecha_inicial.autoCalendar.Year = {'$ (= Max ([fecha_inicial.autoCalendar.Year]) - 1)'}>} sales)

valmanar
Contributor III
Contributor III
Author

Hi Michael, thank you for the answer, I explain the tests:

1.- The Dual field for Year and YearStart creates Sense automatically, if you really use date.autoCalendar.Year does not return a year but a date I think leads to confusion, especially when using it as I in set analytical since returns something you do not expect.

2. The statement: = Max ([initial_date.autoCalendar.Year]) - 1 in a text field returns the numeric value of the date: 427135 for year 2017 selected

3. The statement: = Max (year ([initial_dataCalendar.Year])) - 1 in a text field returns the year, if I have not selected anything in the filter panel the greater, when I select some year in the panel Filter shows me the above. Ok.

3. If this same sentence that in a text box works ok the transfer to a Kpi with the sentence:

Sum ({$ <[start_date.autoCalendar.Year = {$ (= Max (year ([initial_date.autoCalendar.Year])) - 1)}>} sales)

Does not work, if I have nothing selected shows the sales of the previous year to the maximum of the filtering panel, but if I select any year it only shows me 0.

Thanks for the help and regards.

valmanar
Contributor III
Contributor III
Author

Thanks for your reply but I'm sorry to say that it did not work.

Thanks and regards.

valmanar
Contributor III
Contributor III
Author

Hi Sunny, thanks for the information, what I do not explain is that if it is a recognized bug since 2015, is there still this bug in the latest version of 2017? Inexplicable.

Thanks and regards.

sunny_talwar

If you read through the post, you will find some recent responses which still hints that this is still a bug

Anonymous
Not applicable

Your test results make sense to me.  As for the final step, I see a few problems, maybe you just mistyped  here, but check again, notice three bold characters:

Sum ({$ <[start_date.autoCalendar.Year]= {"$ (= Max (year ([initial_date.autoCalendar.Year])) - 1)"}>} sales)


If it doesn't help - use a "regular" master calendar instead auto.