Announcements
cancel
Showing results for
Did you mean:
Creator

## Show total for year grouped on dimension regardless of month selected

Hello all,

I have a straight table chart, which has an expression calculating the efficiency of a task. This is straightforward and calculates the proportion of sub-tasks completed within a certain time-frame for each task:

/

The table shows the efficiency of the task at an individual level as well as the year-to-date efficiency at a country level. The 'YTD Efficiency %' expression simply uses the TOTAL function to display the YTD value by country:

/

When the Year is selected, the table provides the data as needed:

 Country Task ID Month Task Efficiency % YTD Efficiency % Total 72.70% Germany 48254 Jan 2019 80.78% 72.70% Germany 50231 Mar 2019 10.13% 72.70% Germany 50782 Mar 2019 88.02% 72.70% Germany 50794 Mar 2019 92.44% 72.70% Germany 51987 Mar 2019 100.00% 72.70% Germany 53486 Apr 2019 23.90% 72.70% Germany 55592 Apr 2019 100.00% 72.70% Germany 55699 May 2019 84.36% 72.70% Germany 56778 May 2019 72.93% 72.70% Germany 58323 May 2019 100.00% 72.70% Germany 58770 Jun 2019 47.50% 72.70% Germany 60045 Jun 2019 100.00% 72.70% Germany 60876 Jul 2019 81.40% 72.70% Germany 62034 Sep 2019 81.38% 72.70% Germany 62298 Sep 2019 62.80% 72.70% Germany 64554 Oct 2019 80.33% 72.70% Germany 65870 Nov 2019 74.13% 72.70% Germany 66045 Nov 2019 90.26% 72.70% Germany 68002 Dec 2019 97.45% 72.70% Germany 68322 Dec 2019 91.03% 72.70% Germany 68389 Dec 2019 72.11% 72.70%

What I am trying to achieve however, is to have the table show the YTD Efficiency % also when a month is selected. It currently shows the monthly total when a given month is selected:

 Country Task ID Month Task Efficiency % YTD Efficiency % Total 84.78% Germany 68002 Dec 2019 97.45% 84.78% Germany 68322 Dec 2019 91.03% 84.78% Germany 68389 Dec 2019 72.11% 84.78%

I do want the table to only show the tasks for the month, but to display the YTD total, i.e.:

 Country Task ID Month Task Efficiency % YTD Efficiency % Total 84.78% Germany 68002 Dec 2019 97.45% 72.70% Germany 68322 Dec 2019 91.03% 72.70% Germany 68389 Dec 2019 72.11% 72.70%

I have used set analysis with 'Month=', to ignore the month selection, but this then lists all tasks for the year, whereas I want the table to show only those for the month selected.

Is there a more effective solution which would provide the view in the final table above, when that month is selected?

Thanks very much.

Labels (3)

• ### Total

1 Solution

Accepted Solutions
Creator
``````Count(TOTAL <[Year],[Country]> {<[Sub Task Status] = {'Within Goal'}, Month=,

or
Aggr(Nodistinct Count({<[Sub Task Status] = {'Within Goal'}, Month=,

Try both.

Hope one of above expression  will also resolve the problem.

3 Replies
MVP

Try this

``````If(Count([Task ID]) <> 0,
)``````
Creator
``````Count(TOTAL <[Year],[Country]> {<[Sub Task Status] = {'Within Goal'}, Month=,

or
Aggr(Nodistinct Count({<[Sub Task Status] = {'Within Goal'}, Month=,

Try both.

Hope one of above expression  will also resolve the problem.

Creator
Author

Thank you very much. The second solution provides exactly what I need.

The first produces the correct calculation also, but displays all Tasks for the year. The second only shows those for the selected month.

Thank you also, Sunny.

Much appreciated.

Community Browser