Show total for year grouped on dimension regardless of month selected

[Qlik_ULG]

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:

Count({<[Sub Task Status]={'Within Goal'}>}[Task ID])
/
Count([Task ID])

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:

Count(TOTAL<[Year],[Country]>{<[Sub Task Status]={'Within Goal'}>}[Task ID]))
/
Count(TOTAL<[Year],[Country]>[Task ID])

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.

[sunny_talwar]

Try this

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

[sagarjagga · Accepted solution]

Count(TOTAL <[Year],[Country]> {<[Sub Task Status] = {'Within Goal'}, Month=,
Year=P(Year)>}[Task ID])/
Count(TOTAL <[Year],[Country]> {<Month=,Year=P(Year)>} [Task ID])

or
Aggr(Nodistinct Count({<[Sub Task Status] = {'Within Goal'}, Month=,
Year=P(Year)>}[Task ID]),Country,Year)/
Aggr(Nodistinct Count({<Month=,Year=P(Year)>} [Task ID]),Country,Year)

Try both.

Hope one of above expression  will also resolve the problem.

[Qlik_ULG]

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.