Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
leo_pulecio
Contributor II
Contributor II

Add the last 12 months

Good day, I need help with something, some time ago I did a calculation in Qlik Sense to know the expenses of the last 12 months. In other words, if I verify
December 2018 = I must obtain the expenses from December 2017 until November 2018.
January 2019 = I must obtain expenses from January 2018 to December 2018 and so on every month.

Now I need help with something, I think it's a bit more complex, I need to add a condition for the calculation, let's say that in January I had 9 different expenses, in February I had the same 9 expenses and 23 new expenses, in March the same ones mentioned above and 10 new expenses and so on.
What I need is to calculate the expenses of the last 12 months as long as the expenses have been made 12 or more times.

I add an excel file where I have the columns:
* Expenses: is a name or a consecutive that identifies the expenses incurred.
YEAR_: in which the expenditure is made for the first time.
N_MES: in which the expenditure is made for the first time.
N_Year: in which the expense was repeated.
MES_RECUPERACION: in which the expense was repeated.
Fechab: is the column where the year and the month of repetition of the expenditure are concatenated.
RECOVERED: It is the column where the value of the expense is positive or negative.
CANT_MESES, is where I can see the amount of times the expense has been repeated.


As an example of the error I have and what I should do, I place two additional columns:
The value that Qlik Sense takes and the value it should take.
In both you can see the formula with the cells that add up.

The value that Qlik Sense brings out, shows a value in making this formula

 

Suma ({<N_YEAR, MONTH_RECUPERATION =, dateb = {"> = $ (= date (addmonths (max (dateb), - 12), 'YYYY-MM-DD')) <$ (= date (max (dateb) , 'YYYY-MM-DD')) "}>} If (CANT_MESES> = 12, RECUPERADA))

 

 

I hope to help me, because I have been trying for several days to solve it.
Thank you all

3 Replies
vvira1316
Specialist II
Specialist II

Can you please advise in your excel what all records gives you 39874 for January 2016 and 283891 for February 2016.

 

leo_pulecio
Contributor II
Contributor II
Author

Excuse me I do not understand what you want to tell me.

 
The excel file contains data from 2015 to 2018, what I put forward is an example of what I need to do.
I explain again, I hope you understand me.
What I have in the expenditure field are actually some numbers of some contracts that were made, since they are contracts because they generate expenses from the moment of their hiring until today.
In January 2015, 9 contracts were made, these generated expenses during all the months of 2015 and to date they continue to generate expenses, so in January 2016 I must calculate the value added by these 9 contracts throughout 2015.
In February 2015, 23 more contracts were made, which also generate expenses every month of 2015 and still generate expenses, so in February 2016 I must calculate the value added by the 23 contracts made in February 2015 and add the 9 that were made in January.
So each of the months, contracts are added and they add what they have from previous months.
 
What is the condition to perform the calculation? The contract must have more than 12 months of validity, and I must calculate what my expenses were with those contracts but in the last 12 months.
 
I'll give you the last example, I hope you make me understand.
 
Today we are in February 2019, I have to calculate the expenses generated by the contracts that I purchased in February 2018 back, (contracting date from January 2015 to February 2018). From each of these contracts I need to know how much my expenses were in the last 12 months.
 
For that reason I am entangled with the formula because I can calculate the expenses of the last 12 months but I must add the condition that the contract that I am reviewing is 12 months or more in force.
 
I hope you can help me.
leo_pulecio
Contributor II
Contributor II
Author

Hello good day, do you have any idea how I can do it?