Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
leo_pulecio
Contributor III
Contributor III

Add last 12 months

Good afternoon, I need help with something, a while ago in the sense of a desk for the cats of the last 12 months. In other words, if I check
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 in January I had 9 different expenses, in February I had the same 9 expenses and 23 new expenses, in February 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
Expenditure
YEAR and MONTH in which the expenditure is made for the first time
YEAR and MONTH in which the expense was repeated.
DATE_REPETITION, is the column where the year and the month of repetition of spending is concatenated.
VALUE_GASTO
QUANTITY_MESES, is where I can see the amount of times the expense has been repeated.


As an example of what I should do, I put these two additional columns: where you see the formula of the bristles that add up.

Value that brings out common sense, is the value that shows me when making this formula:
Sum ({<N_YEAR, MONTH_RECUPERATION =, dateb = {"> = $ (= date (addmonths (max (dateb), - 12), 'YYYY-MM-DD')) <$ (= date (max (dateb) , 'YYYY-MM-DD')) "}>} If (CANT_MESES> = 12, RECOVERED))

Value that you should take out.


I hope to help me, because it is trying for several days to solve it.
I thank you all
0 Replies