Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
See why BI users voted Qlik #1 in 11 categories. GET REPORT
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

Mothly Count

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,

1 Solution

Accepted Solutions
Highlighted
Partner
Partner

I think I solved it by using an AsOf table 

image.png

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

12 Replies
Highlighted
Partner
Partner

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.
Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Highlighted
Creator II
Creator II

Hi @Vegar ,

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

Highlighted
Partner
Partner

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

CurrencyDateRate
USD_INR01-04-201975
USD_INR02-04-201975
USD_INR03-04-201975
Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Highlighted
MVP & Luminary
MVP & Luminary

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

Highlighted
Partner
Partner

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

Work smarter, not harder
Highlighted
Creator II
Creator II

Hi @Vegar ,

I'm Sorry this is the file

Highlighted
Partner
Partner

Does this input.

IDResignExit
12015-10-27 
22014-01-06 
32014-09-29 
42015-02-162015-04-15
52015-05-01 
62015-09-262016-09-25
72016-01-05 
82015-02-212015-08-15
92015-02-16 
102015-01-05 

 

Give you this output?

MonthCount(Resigned)Count(Active)
Jan49251
Feb28223
Mar17206
Apr23183
May31152
Jun8144
Jul36108
Aug1890
Sep3456
Oct551
Nov2427
Dec123

 

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

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Highlighted
Creator II
Creator II

Hi @Vegar ,

Are you checking the Sheet 3 in the file

Highlighted
Partner
Partner

I think I solved it by using an AsOf table 

image.png

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post