Hi.
I have a date field, called 'Date'. It is populated with individual days for two complete months (e.g. 01 Apr 2016 through 31 May 2016).
I have a variable, vMonth2, which is the MonthName of the latter month (e.g. May 2016).
I have been trying to create a calculated dimension for a line chart, that gives all the days of the second month (e.g. 01 May 2016 through 31 May 2016) regardless of selections in other dimensions. In essence, I want all of the dates to show along the X axis, whether there are corresponding data points for those dates or not.
I started out with this calculated dimension: =If(MonthName(Date)='$(vMonth2)',Date,null())
But it falls over as soon as a selection is made in another dimension (let's say for 'Sales') where there are no corresponding data points for the last few days of the month. The date dimension ends at, for example, 24 May, not 31 May.
If I instruct the chart expression (=sum(Sales)) to allow null values, the line goes bonkers because it then includes Sales for all dates of the previous month (April).
So ... I stand to be corrected, but have decided I probably need to include an aggr() function in my calculated dimension, and somehow instruct it to step through all dates belonging to the complete set of dates applicable to May 2016.
But I cannot get the syntax correct. Can any of you give me an idea of what that should look like, or otherwise suggest a way around the problem?
Thanks,
Pete
