topic Re: Date Expression Showing Null Values in QlikView

Date Expression Showing Null Values

Anonymous — Mon, 31 Jul 2017 18:28:03 GMT

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

Re: Date Expression Showing Null Values

md_qlikview — Mon, 31 Jul 2017 20:05:50 GMT

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)

Re: Date Expression Showing Null Values

Anonymous — Mon, 31 Jul 2017 20:44:41 GMT

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.

Re: Date Expression Showing Null Values

md_qlikview — Tue, 01 Aug 2017 15:34:57 GMT

Hi Jacob,

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

Thnx

Re: Date Expression Showing Null Values

Anonymous — Tue, 01 Aug 2017 15:53:05 GMT

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'))

Re: Date Expression Showing Null Values

Anonymous — Wed, 02 Aug 2017 20:11:37 GMT

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.