Mothly Count

[Sameer9585]

Hi,

I have a dataset which is having ID,Termination and Resignation and now I need the count of ID who have applied for resignation month wise and next is that I need to create an other measure in such a way that I need the cumulative difference from complete count of ID monthly.

Ex: If total 10 are there and 2 left on Jan I need Count as 8,if 3 left on feb I need count as 8-3=5 so on..

Below I provide the data set I am using in it Sheet 3 is to be focused.

Thanks,

[Vegar]

I see your sample excel, but could you please provide an desired output table as well? I have trouble understanding what you are trying to do.

[Sameer9585]

Hi @Vegar ,

Below I provided the output table I need the results exactly the same.

[Vegar]

I don't think you posted the correct excel. The excel you posted contains currency data. Or am I missunderstanding your problem?

Currency	Date	Rate
USD_INR	01-04-2019	75
USD_INR	02-04-2019	75
USD_INR	03-04-2019	75

[marcus_sommer]

I have the impression that your output-file didn't match to your example-file - it looked quite different.

Therefore it's not exactly clear what do you want to do but I assume it's some kind of measuring active / inactive ID's over the time. In general it should be possible with some if- respectively set analysis conditions which queries if the dates are smaller/greater respectively between your time-spans. But this approach could be quite difficult and might only work for certain measures/objects.

Quite common is therefore another approach which fills the NULL's from the termination with today() or yearend(today()) or something similar and used then an IntervalMatch to distribute it to real dates which are linked to a master-calendar. Afterwards you could use rather simple expressions like:

count(distinct ID)

within a chart with a YearMonth dimension.

- Marcus

[JordyWegman]

Hi Sameer,

You can do a Count(TOTAL Distinct ID) and then subtract Count(Distinct ID) for each month in combination with an above to subtract the months.

Jordy

Climber

[Sameer9585]

Hi @Vegar ,

I'm Sorry this is the file

[Vegar]

Does this input.

ID	Resign	Exit
1	2015-10-27
2	2014-01-06
3	2014-09-29
4	2015-02-16	2015-04-15
5	2015-05-01
6	2015-09-26	2016-09-25
7	2016-01-05
8	2015-02-21	2015-08-15
9	2015-02-16
10	2015-01-05

Give you this output?

Month	Count(Resigned)	Count(Active)
Jan	49	251
Feb	28	223
Mar	17	206
Apr	23	183
May	31	152
Jun	8	144
Jul	36	108
Aug	18	90
Sep	34	56
Oct	5	51
Nov	24	27
Dec	1	23

I don't see how the logic? Please explain.

[Sameer9585]

Hi @Vegar ,

Are you checking the Sheet 3 in the file

[Vegar · Accepted solution]

I think I solved it by using an AsOf table