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

[mcsshg2011]

Hi, 

It may be related to selection you have when doing analysis. there are 2 options:

(a) use the 1 for Set analysis to nullify it as suggested; OR 

(b) load separate year and month as isolated tables that do not link to your Fact table.  Then you can just use them for your calculation of days.

[Shahzad_Ahsan]

Hi
I am not getting your answer a but for answer b, If i create isolated table for month and year and their No of Days. So I have to update every month. I don't think this is the right option.

[mcsshg2011]

Hi, 

I have done some testing and may know where your problems could come.  OrderDate = Date field in my transaction file.

With Master Calendar, the Auto-generated code of the field Month has value =  Jan, Feb, Mar etc.

So, to display the Month as number 1,2 and 3, the expression is =Num(Month(OrderDate)).  The name of Filter pane will still show as "Month" (see attached).

So, when referencing it in the formula, instead of using the field name "Month", it should be the formula as follows:

If(Index(GetFieldSelections([=Num(Month(OrderDate))]),1)>0,31,0)+
If(Index(GetFieldSelections([=Num(Month(OrderDate))]),2)>0,28+If(Fmod(GetFieldSelections(Year),4)=0,1,0),0)+
If(Index(GetFieldSelections([=Num(Month(OrderDate))]),3)>0,31,0)+
If(Index(GetFieldSelections([=Num(Month(OrderDate))]),4)>0,30,0)+
If(Index(GetFieldSelections([=Num(Month(OrderDate))]),5)>0,31,0)+
If(Index(GetFieldSelections([=Num(Month(OrderDate))]),6)>0,30,0)+
If(Index(GetFieldSelections([=Num(Month(OrderDate))]),7)>0,31,0)+
If(Index(GetFieldSelections([=Num(Month(OrderDate))]),8)>0,31,0)+
If(Index(GetFieldSelections([=Num(Month(OrderDate))]),9)>0,30,0)+
If(Index(GetFieldSelections([=Num(Month(OrderDate))]),10)>0,31,0)+
If(Index(GetFieldSelections([=Num(Month(OrderDate))]),11)>0,30,0)+
If(Index(GetFieldSelections([=Num(Month(OrderDate))]),12)>0,31,0)
+
If(Index(GetFieldSelections([=Num(Month(OrderDate))]),Num(Month(Today())))>0,
(Today()-MonthStart(Today())+1)-
(MonthEnd(Today())-MonthStart(Today()))
,0)

[mcsshg2011]

Hi, 

No, you don't need to do every month.

You just need the following:

Month1:

load * inline [
Month1
1,2,3,4,5,6,7,8,9,10,11,12];

Year1:
Load distinct Year(OrderDate) as Year1 Resident SalesData$; 

Assuming SalesData$ is the name of your FACT table.