topic Unwanted document level calculation in QlikView

Unwanted document level calculation

Wed, 28 Sep 2011 10:14:02 GMT

I logged a question last week which Jason Michaelides kindly supplied me with the following solution:

COUNT({<Employee={"=SUM({<[Time category group] ={Productive,Billable}>} [Project time])/SUM([Project time])< 0.49999"} >} DISTINCT Employee)

i.e. supply the number of employees where their productive and billable projects amount to less than 50% of their work (each employee will have a number of different entries (the projects))

This works perfectly when I displayed the answer in a text box. I have now been asked to map this figure over different periods and place them in a bar chart (Dimension axis = period and Expression axis = the above calculation)

I have defined a variable vRed as the above calculation (without an equals sign at the front) and used the following calculation on the chart (without an equals sign at the front):

num($(vRed)/Count(Distinct Employee),'##.#%')

The problem I have is when the calculation is placed in a graph it first calculates vRed at a document level and then applies the Count(Distinct Employee)at the period level.

I am not very experienced with set analysis, but from what I have read the ‘=’ is an instruction to calculate at the document level, so I am assuming the ‘=’ in the middle of the vRed calculation is causing my problem?

If this is the case, how do I get round this problem? I have seen Do While loops, but I am not sure that these are used in expression calculations.

Regards

Jason

Unwanted document level calculation

stephencredmond — Wed, 28 Sep 2011 11:35:06 GMT

Hi,

If vRed is defined without an = sign then it should not be calculated at the document level.

To check what is happening, change your chart to a straight table and make the label for the express blank (i.e. it defaults to <use expression>). When the straight table displays, the actual expression that is being used - after the dollar expansion - will be visible and that should help you troubleshoot what is happening.

Regards,

Stephen

Unwanted document level calculation

Wed, 28 Sep 2011 12:51:40 GMT

Hi Stephen,

Thanks for your suggestion, but unfortunately the calculation just appears as if I had written it ‘long hand’.

The way it is calculating the answer is as follows:

- It looks at the data for all periods and calculates how many Employees are <50%, say 10 people.

- It then looks at the specified period and checks how many of those 10 people have data in that period – say 8.

- It will then use the 8 as the numerator and divides it by the number of Distinct employees in that period

Obviously I want it to use the calculated count of employees <50% for the specified period to be the numerator.

Regards

Jason