Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
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:

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:

CountryTask IDMonthTask Efficiency %YTD Efficiency %
Total  72.70% 
Germany48254Jan 201980.78%72.70%
Germany50231Mar 201910.13%72.70%
Germany50782Mar 201988.02%72.70%
Germany50794Mar 201992.44%72.70%
Germany51987Mar 2019100.00%72.70%
Germany53486Apr 201923.90%72.70%
Germany55592Apr 2019100.00%72.70%
Germany55699May 201984.36%72.70%
Germany56778May 201972.93%72.70%
Germany58323May 2019100.00%72.70%
Germany58770Jun 201947.50%72.70%
Germany60045Jun 2019100.00%72.70%
Germany60876Jul 201981.40%72.70%
Germany62034Sep 201981.38%72.70%
Germany62298Sep 201962.80%72.70%
Germany64554Oct 201980.33%72.70%
Germany65870Nov 201974.13%72.70%
Germany66045Nov 201990.26%72.70%
Germany68002Dec 201997.45%72.70%
Germany68322Dec 201991.03%72.70%
Germany68389Dec 201972.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:

CountryTask IDMonthTask Efficiency %YTD Efficiency %
Total  84.78% 
Germany68002Dec 201997.45%84.78%
Germany68322Dec 201991.03%84.78%
Germany68389Dec 201972.11%84.78%

 

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

CountryTask IDMonthTask Efficiency %YTD Efficiency %
Total  84.78% 
Germany68002Dec 201997.45%72.70%
Germany68322Dec 201991.03%72.70%
Germany68389Dec 201972.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.

 

 

 

 

Tags (3)
Labels (3)
1 Solution

Accepted Solutions
Highlighted
Creator
Creator

Re: Show total for year grouped on dimension regardless of month selected

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.

View solution in original post

3 Replies
Highlighted

Re: Show total for year grouped on dimension regardless of month selected

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])
)
Highlighted
Creator
Creator

Re: Show total for year grouped on dimension regardless of month selected

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.

View solution in original post

Highlighted
Creator
Creator

Re: Show total for year grouped on dimension regardless of month selected

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.