Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

wanyunyang
Contributor

count number of months

I have a date 11/2017 and a year filter. I'm trying to count the number of months. I

f I select year 2016 in the filter, it returns 0. If select 2017, returns 2 (11/2017, 12/2017). If select 2018, it will count till today, so returns 7 (11/2017, 12/2017, 01/2018, 02/2018, 03/2018/, 04/2018, 05/2018).

What should the function be for this?

Thanks in advance!

8 Replies

Re: count number of months

I have a date 11/2017 and a year filter.

Where is this date coming from?

wanyunyang
Contributor

Re: count number of months

year(date) is the year filter.

Re: count number of months

My question was where is this date coming from

wanyunyang
Contributor

Re: count number of months

Just Nov 2017

Re: count number of months

Not sure I understand, is there a sample your can share to explain your problem?

jerem1234
Valued Contributor II

Re: count number of months

You can try either of these:

=count({$*<Date = {'>=$(=date(date#('11/2017', 'MM/YYYY')))'}>}Date)

=count(if(Date>=date#('11/2017', 'MM/YYYY'), Date))

This assumes that your Date field is in the format of 'MM/YYYY' hence it is just a standard count. You might have to use distinct in your expression as well depending on your data:

=count(DISTINCT{$*<Date = {'>=$(=date(date#('11/2017', 'MM/YYYY')))'}>}Date)

I also assumed your date 11/2017 is hard-coded or a certain date you have in mind. If its dynamic, you might have to use variables and tweak the code.

Hope this helps!

rafatashiro
New Contributor III

Re: count number of months

Hi,

U can try this:

Count( {<Date= {">=Date#(11/2017,'MM/YYYY')<=Max(Month))"}>} Month )

Re: count number of months

May be this?

Count( {<Year_Month= {">=$(=MinString(Year_Month))<=$(=MaxString(Year_Month)))"}>} Month)

Community Browser