Qlik Community

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 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
Contributor III
Contributor III

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.

 

 

Highlighted
Creator III
Creator III

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.
Highlighted
Contributor III
Contributor III

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)

Highlighted
Contributor III
Contributor III

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.