Set Analysis: dynamic dimension

[jakobjosef]

Dear Qlik-View-Community,

I think i need your help again, maybe someone can help me out with following problem:

In a table, I want to show the employees attendance (in hours) on a monthly basis, but only for employees, who had sales this month.

I tried to create a regular dimension for the month (because I want to see the value on a monthly basis) and a dynamic dimension, which shows all employees, who had sales this month (the dynam. dimension looks like: if ( Sales >=0, Name of employee).

Until here everything is fine, i can see the month and all employees, who had sales this month.

Now I want to show as well the attendance (in hours) of the entire month. Employees work 8 hours a day (so the data set is per day).

If I try it with "Sum(attendance time)", qlikview only shows the Sum of the days, where the employee had sold something. So if he sold every second day something, I get the attendance-Sum of every second day, but i want the attendance-time of the entire month.

Maybe someone knows how to deal with this problem.

Greetings from vienna,

Jakob

[niclaz79]

Hi,

This is because your dimension (Sales >=0) is limited to the months where sales take place and automatically excludes those in the dimension. If you use the normal dimension it should work as intended.

[sibin_jacob]

Dimension keep as  "Name of employee"

Go to presentation tab of the chart

then check on the" suppress zero values"

[jakobjosef]

Thank you for your answers, Niclas & Sibin.

Unfortunately it seems to me I did not declare me right:

The problem is the sum of the attendance-time: Qlikview aggregates for each day, where an emplyee sold something, 8 hours. So if somebody sold on 4 specific days something, qlikview calculates like:

"4 days, on which something got sold" * 8 hours/day = 32 hours.
But the employee was present on 22 days this month, so it should be 22*8= 176 attendance-time this month.

If it is still hard to understand what I want I could create an example.

Greetings

Jakob

[niclaz79]

Hi, it's easy to understand your question but without seeing the data model behind it's hard to figure out what is wrong. Do you have the qvw available for us to look at?

[MWiciok]

You can get this with an aggr-function:

If this is your data-model, then the expression

If(Sum(Aggr(Sum(Sales), Employee, MonthExp)) > 0, Sum([Working Hours]))

will do the job. Maybe you can get the Sum(Aggr(...)) in the Set-Analysis of the Sum([Working Hours]), but I am not sure.

Matthias

[jakobjosef]

I was not able to post the answer with attached file, so I uploaded it here:

set analysis 2 - dynamic dimension?

Greetings

Jakob