Set analysis with autoCalendar

[valmanar]

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.

[valmanar]

Sorry Michael, it has actually been a typing error, the sentence is:

sum({$<[fecha_inicial.autoCalendar.Year] = {$(=max(year([fecha_inicial.autoCalendar.Year]))-1)}>} ventas)

[Anonymous]

Still, you missed the double quotes around $(=...).

[valmanar]

Today I am distracted writing, I have tried with the double quotes, with the simple ones, etc, I have not had luck.

[Anonymous]

OK, in this case a master calendar is your best bet.

[valmanar]

Thank you Michael.

[sunny_talwar]

Another option would be to create a year field in your fact table itself in case you don't wish to create a master calendar.

[cristian_ionesc]

November 2018, the bug seems to be still here...

I have the following formula that I'm testing to be able to select the last week of the current year:

='Week '&Max({$<[AA_DAD_W_SC.Week.autoCalendar.Year]={'$(=Max([AA_DAD_W_SC.Week.autoCalendar.Year]))'}>}[AA_DAD_W_SC.Week.autoCalendar.Week])

It doesn't work .

Initially I had a special field in my data called 'Year' which was an integer - it worked perfectly - but then I changed my ETL process and my data source so that I would benefit of Qlik Sense capability to generate a calendar for me...

[cristian_ionesc]

I solved it by pre-calculating my Year and Week in the data source...