Skip to main content
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
Showing results for 
Search instead for 
Did you mean: 
Contributor II
Contributor II

Set a starting point from a field in a measure

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:



Accumulated days: 


Taken days:


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!

Labels (4)
0 Replies