Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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)
Still, you missed the double quotes around $(=...).
Today I am distracted writing, I have tried with the double quotes, with the simple ones, etc, I have not had luck.
OK, in this case a master calendar is your best bet.
Thank you Michael.
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.
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...
I solved it by pre-calculating my Year and Week in the data source...