Date Expression Showing Null Values

[Anonymous]

Hi,

I've created a graph with dates along the x-axis and the expression I've used for the measure looks like this:

count({<[Period.autoCalendar.Date]={'>$(=AddMonths(Max([Period.autoCalendar.Date]),-6))'}>}(Field Name))

What I want to show is the last 6 months of data. What the chart is actually doing is showing data for the last 6 months but showing all available months of data, going back to all available dates, with those months greater than 6 months in the past as blank. E.g. - showing the months in the x-axis but without any data.

The checkbox for including null values is not checked in the dimension is not checked so I'm thinking it must be something with the above expression.

Thanks

[md_qlikview]

Hi Jacob,

Try below expression. This may help you.

Count({$<[Period.autoCalendar.Date]={">=$(=Date(AddMonths(Max([Period.autoCalendar.Date]),-6)))<=$(=Date(Max([Period.autoCalendar.Date])))"}, Year=, Month=, [Period.autoCalendar.Date]=>}Field Name)

[Anonymous]

Unfortunately that didn't work. The only thing that changed was data for the prior months is showing in the chart rather than null values for those dates.

[md_qlikview]

Hi Jacob,

Kindly share sample file if possible. it will be easier to rectify exact problem

Thnx

[Anonymous]

try this:

=num(count({<LinkCommonDate={">=$(vlast6MonthMinDate)<=$(vlast6MonthMaxDate)" }>}fieldName),'#,##0')

where make variables :

vlast6MonthMinDate=floor(Date(MonthStart(  AddMonths( max(LinkCommonDate),-6)),'YYYY-MM-DD'))

vlast6MonthMaxDate=floor(Date(MonthEnd(   AddMonths( max(LinkCommonDate)   ,-6)),'YYYY-MM-DD'))

[Anonymous · Accepted solution]

Seems like my original formula does indeed work after playing around with the Date Dimension. All I had to do was change the Limitation to "Top 6" and then uncheck the box that says Show others.