Show 0 values on bar chart but apply selection to dimension

[alidak]

Hi guys,

I need to show all values (including 0 values) for a dimension in a bar chart, but I also need to have the selection applied to the dimension when a selection is made.

I have the following dimensions in a bar chart:

Date
Location

My expression is as follows:

=Sum(VehicleCount) + 0*Count({1} VehicleCount)

This expression forces the 0 values to show on the chart.

However, if I choose a specific date or date range, the Date dimension is not limited to only the selection but shows all dates.

I need to show the dates that falls within the selection - even if 0 values applies.  But the dates that are outside of the selection should not appear on the chart as long as the selection is active.

[vikasmahajan]

Hi,

You need to make sure your model is connected with master calendar if not you can implement the master as per following code:

MasterCalendar:
LOAD
    OrderDate AS OrderDate,
    WEEK(OrderDate) AS Week,
    YEAR(OrderDate) AS Year,
    MONTH(OrderDate) AS Month,
    DAY(OrderDate) AS Day,
    WEEKDAY (OrderDate) AS Weekday,
    'Q' & CEIL(MONTH(OrderDate) / 3) AS Quarter,
    DATE(MONTHSTART(OrderDate), 'MMM-YYYY') AS MonthYear,
    WEEK(OrderDate) & '-' & YEAR(OrderDate) AS WeekYear ;

Load
  Date(MinOrderDate+IterNo()-1) as OrderDate
While MinOrderDate+IterNo()-1  <= MaxOrderDate;
  
LOAD
    Date(MIN(FieldValue('OrderDate',recno()))) AS MinOrderDate,
    Date(MAX(FieldValue('OrderDate',recno()))) AS MaxOrderDate
autogenerate FieldValueCount('OrderDate');

[alidak]

Thanks for your reply, Vikas.

I already have a mastercalendar linked to this table which I am referencing, but it does not solve the issue.

I think the problem would be that I am forcing the chart to show all values with set analysis in the expression (Sum(VehicleCount) + 0*Count({1} VehicleCount) but I also need it to apply the selection on the date field only and only if a selection in the date field has been made.

I just don't know how to get this across to the chart.  🙂