Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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!
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)
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
Try to add :
[FECHA.autoCalendar.Year] ={"$(=max([FECHA.autoCalendar.Year]))"}
=>
Sum ({<[FECHA.autoCalendar.Month] = {$ (vPriorMonth)}, [FECHA.autoCalendar.Year] ={"$(=max([FECHA.autoCalendar.Year]))"}>}) VALUE)
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.
can you share your qvf file? even a sample one to work with?
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!