Compare the period from one month to the previous with the year change

[Anonymous]

Hello! How are you ?, I have a query since I am having problems comparing periods when I move from one year to another, for example if I am positioned in January 2017 and I want to see what December 2016.

My case is the following, in a simple way, I have selected the field MES (month) (built with the Calendar on the main field FECHA) and the field AÑO (year) (idem. Previous), then I want to see the sales generated in the last month taking into account That the AÑO (year) changes.

Situation: I have a selection in the Field [FECHA.autoCalendar.Month] (Month) and [FECHA.autoCalendar.Year] (Year).

The formula that I am using (contains SET ANALYSIS function) and that serves me to buy months within the same year is as follows:

Sum ({<[DATE.autoCalendar.Month] = {$ (vPriorMonth)}>}) VALUE)

The variable vPriorMonth is as follows: = Month (addmonths (max (DATE, - 1))

I did this but DO NOT WORK for the change of Year: SUM ({<[DATE.autoCalendar.Month] = {$ (vPriorMonth)}, [DATE.autoCalendar.Year]>} VALUE). It does not work properly since it makes me add up all December sales of all the years, since it leaves me released the field [FECHA.autoCalendar.Year] (Year).

It is understood? Can anybody help me?

Thank you very much

[OmarBenSalem]

I don't like to work with the auto calendar. I fill kind of more at ease when having the control of my calendar.

With that being said: with your expression :

SUM ({<[DATE.autoCalendar.Month] = {$ (vPriorMonth)}, [DATE.autoCalendar.Year]>} VALUE)

You're forcing Qlik to work with ALL THE YEARS with the [DATE.autoCalendar.Year] part.

To focus only on the last month of the selected year, you should eliminate it.

Your expression would be sthing like this:

SUM ({<[DATE.autoCalendar.Month] = {$ (vPriorMonth)}>} VALUE)

[Anonymous]

Omar, thanks for your answer, this is the expression that I used like I said on the post: Sum ({<[FECHA.autoCalendar.Month] = {$ (vPriorMonth)}>}) VALUE) . The problem is makes me add up all December sales of all the years, including the year 2016, 2015, 2014 of the month December

[OmarBenSalem]

Try to add :

[FECHA.autoCalendar.Year] ={"$(=max([FECHA.autoCalendar.Year]))"}

=>

Sum ({<[FECHA.autoCalendar.Month] = {$ (vPriorMonth)}, [FECHA.autoCalendar.Year] ={"$(=max([FECHA.autoCalendar.Year]))"}>}) VALUE)

[Anonymous]

I just tried it , The quotes do not seem to be, Sum ({<[FECHA.autoCalendar.Month] = {$ (vPriorMonth)}, [FECHA.autoCalendar.Year] ={"$(=max([FECHA.autoCalendar.Year]))"}>}) VALUE) equally leaving them or removing them does not work.

[OmarBenSalem]

can you share your qvf file? even a sample one to work with?

[Anonymous · Accepted solution]

Omar, I can find the solution!: Sum ({<[FECHA.autoCalendar.Month]={'$(vPriorMonth)'},[FECHA.autoCalendar.Year]={'$(vAÑO)'}>} VALUE)

Where the variable vAÑO is =Year(AddMonths(max(FECHA),-1))

Thank you very much for your time and help!