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]

May be this:

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

or

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

[#3]

Sunny, thank you very much. That almost gets me there! It properly shows the # of Business Days going down the chart column; however, I now have a new problem...

It is now showing EVERY task in the task table as opposed to the ones that were actually performed in the month.

There are MANY tasks in the task table, but only some of them may be performed on any given Month. I only want to display the tasks that were performed each Month. Is it possible to do this?

[sunny_talwar · Accepted solution]

Sorry, I forgot to remove Task from set analysis

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

or

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

[#5]

Thank you!

[#6]

Darn. One more thing I just noticed Sunny...

Requirements:

*Business days must reflect accurately for the month  (complete)

*Tasks displayed should only reflect the tasks that were performed in each month

*Business Day calculation must not be affected by filtering on a task (and select others)

Once I removed [Task] from set analysis, the tasks that were performed showed up nicely. However, when I filter on a specific task, Qlik evaluates the # of business days that task was performed. This makes the calculation invalid. Is there a way to satisfy all of these requirements?

[sunny_talwar]

I don't have a good grasp of your requirement, based on the sample you have provided above, what would be the expected output based on a selection of a specific task? Also, which of the two expressions have worked for you thus far

[#8]

The [# Business Day] calculation is used in other KPIs. This number must stay consistent so the other KPIs are accurate. An example of a calculation that uses this KPI, Estimated Effort = [Estimated Time] / [# Business Days]. If the denominator (# Business Days) is inconsistent from row to row, the calculation will yield inconsistent results.

When I use, Sum(TOTAL <Month> {<BusinessDay={'YES'}>} EventDayCount), I end up seeing ALL tasks from my task table (>50). I only want to see the tasks that were performed in this list.

When I use, Sum(TOTAL <Month> {<BusinessDay={'YES'}, Task= >} EventDayCount), I see just the tasks I want. This is perfect so far.

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

However, when I filter on a Task, I see the # Business Days that the task was performed:

Month	Task	# Business Days
Jan-2016	Meetings	10
Feb-2016	Meetings	6
Mar-2016	Meeting	11
...	...	...

I want to see the following,

Month	Task	# Business Days
Jan-2016	Meetings	20
Feb-2016	Meetings	20
Mar-2016	Meeting	22
...	...	...

[sunny_talwar]

Got it, try this:

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

[#10]

This appears to work... can you explain a little bit how that works? I'm trying to understand it better.