Hey everyone!!! I'm trying to get the final accumulated Holidays per year. I have the initial balance stored in one field, which I need as a starting point to get the final accumulated days over the year, the thing is that I don't know how to use this field just as a starting value and then use it for the next month calculation.
Currently, I am having trouble doing it because of the nature of the Initial and Final Balance. The Final Balance formula is: Initial Balance (stored in a field)+ Accumulated days - taken days. However, this formula must change for the next period to: Initial Balance (previous calculation result)+ Accumulated days - taken days. I have no idea how to take the field value just once and then roll over the formula with the previous result obtained.
Please find an example in Excel of how it should work:
My data in Qlik looks currently like this:
So far I tried this measure for the final balance but is not working because is adding the initial Balance from the field
AGGR(if(Month([Hire Date])=Month(FinalDate) and Seniority>=1 and Seniority<2, 10, if(Month([Hire Date])=Month(FinalDate) and Seniority>=2 and Seniority<3, 12, if(Month([Hire Date])=Month(FinalDate) and Seniority>=3 and Seniority<4, 15, if(Month([Hire Date])=Month(FinalDate) and Seniority>=4,20,0)))) - Sum( distinct(if (Month(Initial_date)= FinalMonth, [Amount]))) + [Saldo al 31.12.2020],KeyEmployee,FinalMonth)
Has anyone ever done something like this? Thank you in advance!