Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

maxicarena
Contributor

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

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

1 Solution

Accepted Solutions
maxicarena
Contributor

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

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!

6 Replies
Partner
Partner

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

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)


maxicarena
Contributor

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

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

Partner
Partner

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

Try to add :

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


=>

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



Highlighted
maxicarena
Contributor

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

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.

Partner
Partner

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

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

maxicarena
Contributor

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

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!