How to count number of days in a month on selection

[Shahzad_Ahsan]

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

[miskinmaz]

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,

[Shahzad_Ahsan]

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]

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]

Hi Mcsshg2011
What is "Month1" and "Year1" here?

[mcsshg2011]

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]

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]

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]

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