Show all selected months regardless of data

[nagprasad]

I've a table, has 3 columns Date (Month-Year), Recordable cases monthly data, Recordable Cases for last 12 months. 

I want to show all the selected months, and display zeros if the months doesn't have data.

I selected last 25 months by default, and I want to show all the 25 months regardless of the data. It shows either all 25 months with incorrect values or it shows the months which had values.

Expressions:

Monthly Data - Sum(RecordableCases)

Last 12 month Total - Sum({<[Reporting Date.Calendar.Date]={"<=$(=date(max([Reporting Date.Calendar.Date]),'DD/MM/YYYY'))>=$(=date(monthstart(addmonths(min([Reporting Date.Calendar.Date]),-12)),'DD/MM/YYYY'))"}
, [Report Period], [Reporting Date.Calendar.Year], [Reporting Date.Calendar.Date], [Reporting Date.Calendar.YearMonth]
>} RecordableCases)

Disabled Zeros in the Add-Ons, otherwise I see 12 more months as per expression.

Attached the qvf file for easily reproduceable. Also attached the expected and actual result screenshot

Any help would be appreciated.

[WildmoserGeorg]

Hi,

can you please post your associated data model?

What do you get if you delete the "TRC Expression" measure from the diagram and the dimension have "Include NULL values" checked?

thx

[nagprasad]

Hi,

Thanks for the response.

If I delete the "TRC Expression" measure, I see the same results. And yes "Include NULL values " are checked.

Thank you !!

[neerajthakur]

Write your expression as  Count({1<[Impacts: Injury Illness Consequence: Classification Recordable]-=''>} [Impacts: Incident ID])

or 

 Count({<[Impacts: Injury Illness Consequence: Classification Recordable]-='',Month=>} [Impacts: Incident ID])

To make it independent of selections and then you will see all fields.

[nagprasad]

It is showing the same dates, not all months. And the numbers are incorrect if I change it this way.

I see all months from Jan-2020 to Mar-2023 or only months which has data shown in the screenshot. But it should show only Jan-2022 to Dec-2022.

Please remember, as part of my calculation, I've to sum last 12 months for each given month as well.

Thanks!!

[neerajthakur]

As per your screenshot you are selecting different things in both the cases just apply that field name in expression as Count({<[Impacts: Injury Illness Consequence: Classification Recordable]-='',Field=>} [Impacts: Incident ID])

So that there is no change on selection of field names in table.

[nagprasad]

If I add those fields to my expression, that numbers are incorrect as those fields will be excluded in the count calculations

[nagprasad]

Rephrased and added more information in the description and attached the qvf file for easily reproduceable.

Any help would be appreciated