
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Date Expression Showing Null Values
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
- Tags:
- previous date
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Jacob,
Kindly share sample file if possible. it will be easier to rectify exact problem
Thnx

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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'))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
