Calculation of population based on previous months
Hi Al,
There is a problem which I am not able to sort out.
I have to calculate population of any organisation based on different dimensions...
Example is attached herewith:
For example I am explaining the data...
There are region, dept, branch office as main dimensions....
there is joining date , resigning date and transition date...
If a person joins the company then joining date is entered and the respectivve transition date and column count is updated as 1.
I he resigns then resign date is insertd and the transition ate will be the resign date and cnt is updated as -1.
I have calculated the cummulative value.
But I am unable to calculate population in charts, as in data year2012 on sep 30 there were 309, people and in oct there were only308, but my chart calculates it wrong.
If I understand your issue correctly, it depends on how population is defined when you examine a period of time, like a year or month of a year, while population is changing, but you want to get one measure back.
I see that you are currently using max(Cumulative) in your chart. This will of course return the maximum value of field Cumulative in the examined period of time. As I understand your post, you want the Cumulative value at the end of the period (I assume that your other measures Exits and Joins are calculated correctly per that point in time?).
If so, I think you need something different from max() function, maybe
=FirstSortedValue( Cumulative, -tran_dt)
Unfortunately, it seems that you can have multiple records for a tran_dt (like in Sep 30, 2012). A plain firstsortedvalue() will return NULL if you have multiple values for the same sort weight value (which is -tran_dt here).
You can use DISTINCT qualifier to get a value back (the first in load order, as far as I remember, so FirstSortedValue(DISTINCT Cumulative, -tran_dt) will return 310 in your case for Sep 30), but I think you want the latest record back.
I would suggest that you either use the timestamp of the changes record rather than the date (if available), or add a small numerical value to the date increasing per record (if these records are coming in chronological order).
But actually my formula was wrong as for different dimensions as dept and region this cumulative value returs wrong population.
actually I need to calculate sum(cnt) which is some of all transition dated less than or equal to seleted date(year/ month), but i am unable to achieve it...
