Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Unwanted document level calculation

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

2 Replies
stephen-a_redmo
Valued Contributor II

Unwanted document level calculation

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

Not applicable

Unwanted document level calculation

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

Community Browser