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.

[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]

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]

Try

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

[valmanar]

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]

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

Thanks and regards.

[valmanar]

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]

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.