Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
The #1 reason QlikView customers adopt Qlik Sense is a desire for a modern BI experience. Read More
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.

 

 

 

 

Labels (3)
1 Solution

Accepted Solutions
Highlighted
Creator
Creator

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

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

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

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.