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

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

Mon, 12 Dec 2016 18:58:46 GMT

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?

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

sunny_talwar — Mon, 12 Dec 2016 19:16:46 GMT

May be this:

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

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

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

Mon, 12 Dec 2016 19:39:46 GMT

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?

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

sunny_talwar — Mon, 12 Dec 2016 19:50:37 GMT

Sorry, I forgot to remove Task from set analysis

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

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

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

Mon, 12 Dec 2016 19:52:27 GMT

Thank you!

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

Mon, 12 Dec 2016 21:07:56 GMT

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?

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

sunny_talwar — Mon, 12 Dec 2016 21:47:32 GMT

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

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

Tue, 13 Dec 2016 13:58:15 GMT

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
...	...	...

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

sunny_talwar — Tue, 13 Dec 2016 14:20:09 GMT

Got it, try this:

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

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

Tue, 13 Dec 2016 14:26:28 GMT

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

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

sunny_talwar — Tue, 13 Dec 2016 14:30:47 GMT

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.

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

Tue, 13 Dec 2016 20:02:08 GMT

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?

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

sunny_talwar — Tue, 13 Dec 2016 20:14:18 GMT

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