Chart Funcationality: How can I ignore evaluation of second dimension (for an expression)?

[#1]

Hello,

I am trying to calculate the number of business days by month. The tricky part is getting Qlik to evaluate this calculation while ignoring the second dimension.

The expression for # Business Days is,       sum( {<BusinessDay={'YES'}, Task= >} EventDayCount)

*EventDayCount is part of my calendar table which is linked to my FACT table

*Task is part of my Task table which is linked to my FACT table

*FACT table contains the records of work and it refers to the calendar table and Task table

This is what the current output looks like,

Month	Task	# Business Days
Jan-2016	Data Processing	20
Jan-2016	Meetings	10
Jan-2016	Client Support	7
Feb-2016	Data Processing	20
Feb-2016	Meetings	6
Feb-2016	Client Support	11

This is my desired output,

Month	Task	# Business Days
Jan-2016	Data Processing	20
Jan-2016	Meetings	20
Jan-2016	Client Support	20
Feb-2016	Data Processing	20
Feb-2016	Meetings	20
Feb-2016	Client Support	20

Even after using set analysis in my expression, Qlik is still evaluating the second dimension and showing me how many business days I have data for each task.

I tried using the aggr function but I could not figure out how to structure it so it will evaluate the first dimension and properly populate the number of days in each row. I also tried precalculating this value in a variable first and then pulling it in, but I have the same problem with Qlik evaluating the second dimension.

Is it possible to get my desired output?

[sunny_talwar]

I am using Avg({<BusinessDay={'YES'}>} 1) to zero out those rows which are not needed. Basically, Avg(1) will be 1 for your selections and 0 for out of selections. To see what I mean create two expressions

1) Sum(TOTAL <Month> {<BusinessDay={'YES'}, Task= >} EventDayCount)

2) Avg({<BusinessDay={'YES'}>} 1)

and see how these two work based on selection in task. 1st expression won't change and 2nd expression will become 0 for rows for non-selected rows.

[#3]

So avg(1) is basically a hack to exclude any rows that aren't selected? This is a very useful tip.

Do I even need the set analysis in the avg function?

[sunny_talwar]

I would use that in order to make sure to make sure that Avg(1) shows 1 for only BusinessDay = 'Yes' and don't change based on selection in BusinessDay just like your main expression. The only thing is that you don't want to ignore selection in Task, because then it won't turn 0 based on selection.

HTH

Best,

Sunny