# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for
Did you mean:
Highlighted
Creator III

## How to count number of days in a month on selection

Hello Friends

I need to count number of days in a selected months. It seem easy but there is some condition.

I have two different filters Year and Month

Suppose I selected year=2019 so the result should be, Number of days from 1 Jan 2019 till Today.

Suppose I selected year=2019 and Month=Jan, So the result should be 31

Suppose I selected year=2019 and Month=Jan and Feb, So the result should be 31+28=59

Suppose I selected year=2019 and Month=Mar and Apr, So the result should be 31+16=47

Suppose I selected year=2019 and Month=Apr, So the result should be 16

Regards

Labels (1)
• ### Qlik Sense

13 Replies
Highlighted
Creator III
create a master calendar with date granularity and then count the date.
Highlighted
if you have date field then use Interval()
try like
Interval(max(date)-min(Monthstart(date)),'D')
or
num(max(date))-num(min(monthStart(date)))

Regards,
Great dreamer's dreams never fulfilled, they are always transcended.
Highlighted
Creator III
Hi Miskinmaz
I followed your idea of counting the dates. But there is one problem, if there is transactions on 20 days on any month and rest 10 days doesn't have any transaction, so the result is showing 20 but I need 30. It should not depend on my date field.
Highlighted
Contributor III

Hi,

Note:  Month1 = value 1 to 12 in the selection pane.

If(Index(GetFieldSelections(Month1),1)>0,31,0)+
If(Index(GetFieldSelections(Month1),2)>0,28+If(Fmod(Max(GetFieldSelections(Year1)),4)>0,1,0),0)+
If(Index(GetFieldSelections(Month1),3)>0,31,0)+
If(Index(GetFieldSelections(Month1),4)>0,30,0)+
If(Index(GetFieldSelections(Month1),5)>0,31,0)+
If(Index(GetFieldSelections(Month1),6)>0,30,0)+
If(Index(GetFieldSelections(Month1),7)>0,31,0)+
If(Index(GetFieldSelections(Month1),8)>0,31,0)+
If(Index(GetFieldSelections(Month1),9)>0,30,0)+
If(Index(GetFieldSelections(Month1),10)>0,31,0)+
If(Index(GetFieldSelections(Month1),11)>0,30,0)+
If(Index(GetFieldSelections(Month1),12)>0,31,0)
+
If(Index(GetFieldSelections(Month1),Num(Month(Today())))>0,
(Today()-MonthStart(Today())+1)-
(MonthEnd(Today())-MonthStart(Today()))
,0)

Highlighted
Creator III
Hi Mcsshg2011
What is "Month1" and "Year1" here?
Highlighted
Contributor III

Month1 is one of my field in my database, which have value 1 to 12 which represents the month (1,2,3,4,5,6,....12).  1 = Jan.

Year1 is one of my field in my database, which have the transaction year (eg: 2016, 2017, 2018)

They are added into the analysis sheet as Filter Pane so that users can select Year and Month.
Highlighted
Contributor III

In short, what it does is:

I have 2 filter pane that allows users to choose the Month and Year.

If users choose Month 1 and 4, which is Jan and April, it will be 31 days + 16 days as in your example.

The index formula is first looking at what month the users select and return the total of days in a month.

EG: At the Index formula, it detects 4  (April) is selected, so, = 30 day.

And then If the selected month = today's month, it will minus the total days of the month (30 days) and get the number of days of the month (16 days 1st April to 16 April using your example).

Highlighted
Creator III
This might be happening because of some selection. If yes you can always nullify it in set analysis or use 1 modifier in set analysis.
Highlighted
Creator III
Hi
I am using master calendar. I have Year and Month field which is extracted from datefield.

I have replaced 'Month1' with 'Month' and 'year1' with 'Year' in your above given expression.

But every time I select an Year and Month from the filter, it always returns 0