Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
Hi @Vegar ,
Below I provided the output table I need the results exactly the same.
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 |
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
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
Hi @Vegar ,
I'm Sorry this is the file
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.
Hi @Vegar ,
Are you checking the Sheet 3 in the file