Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Shahzad_Ahsan
Contributor II

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

Shahzad

Labels (1)
13 Replies
miskinmaz
Contributor II

Re: How to count number of days in a month on selection

create a master calendar with date granularity and then count the date.

Re: How to count number of days in a month on selection

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.
Shahzad_Ahsan
Contributor II

Re: How to count number of days in a month on selection

Hi Miskinmaz
Thanks for your reply.
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.
mcsshg2011
New Contributor III

Re: How to count number of days in a month on selection

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)

Shahzad_Ahsan
Contributor II

Re: How to count number of days in a month on selection

Hi Mcsshg2011
What is "Month1" and "Year1" here?
mcsshg2011
New Contributor III

Re: How to count number of days in a month on selection

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.
mcsshg2011
New Contributor III

Re: How to count number of days in a month on selection

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).

 

 

 

miskinmaz
Contributor II

Re: How to count number of days in a month on selection

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.
Shahzad_Ahsan
Contributor II

Re: How to count number of days in a month on selection

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