Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

nurfatenmunirah
New Contributor II

How to get Number of Days per Month for past six months in bar chart

Hi,

I'm Mun. I'm experimenting to display number of days in a month for past six months range.

For example, when I select June 2017 in the MonthYear filter, the graph will display number of days from January 2017 until June 2017.

Meanwhile, when I select May 2017, the graph should show from December 2016 to May 2017.

Capture.PNG

At the moment, it doesn't work like that. Currently, if I select June 2017, the graph will only show the value for that particular month.

For the measure, which is number of days in a month, I used this expression:

Sum(Aggr(Day(MonthEnd(Max(DateField))),DateField))

Capture1.PNG

Please, kindly advise and share your idea how can I solve this.

Thank you in advance.

Cheers,

Mun

1 Solution

Accepted Solutions
mov
Esteemed Contributor III

Re: How to get Number of Days per Month for past six months in bar chart

This works for me:

Chart dimension is [Month Year]

Chart expression is

count({<[Month Year]={"$(='>=' & date(AddMonths(max([Month Year]),-5),'MMM YY') & '<=' & date(max([Month Year]),'MMM YY'))"}>} distinct DateField)

2 Replies
Highlighted
kkkumar82
Valued Contributor III

Re: How to get Number of Days per Month for past six months in bar chart

First step take the selected date in a variable

Sum({1<Datefield = {">=AddMonths(Date($(Variable)),-6) <=Date($(Variable))"}>} Yourfield)

try this

mov
Esteemed Contributor III

Re: How to get Number of Days per Month for past six months in bar chart

This works for me:

Chart dimension is [Month Year]

Chart expression is

count({<[Month Year]={"$(='>=' & date(AddMonths(max([Month Year]),-5),'MMM YY') & '<=' & date(max([Month Year]),'MMM YY'))"}>} distinct DateField)