Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
ITMercon
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:

ITMercon_1-1616513804111.png

My data in Qlik looks currently like this:

ITMercon_3-1616513985783.png

Measures:

Accumulated days: 

ITMercon_4-1616514050852.png

Taken days:

ITMercon_5-1616514106050.png

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