Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Shahzad_Ahsan
Creator III
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

Shahzad

Labels (1)
13 Replies
miskinmaz
Creator III
Creator III

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

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.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
Shahzad_Ahsan
Creator III
Creator III
Author

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
Contributor III
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)

Shahzad_Ahsan
Creator III
Creator III
Author

Hi Mcsshg2011
What is "Month1" and "Year1" here?
mcsshg2011
Contributor III
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.
mcsshg2011
Contributor III
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).

 

 

 

miskinmaz
Creator III
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.
Shahzad_Ahsan
Creator III
Creator III
Author

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