Isolating a Dimension for Aggregate Functions

[#1]

Hi all

I have the following QlikView data model.

tblDailyLA.CAPACITY is the fact value.

In my report I would like to see the fact value divided by the number of working days in a selected period, dimCalendar.DayCount signifies a working day as a 1 and a weekend as a 0.

Sum(CAPACITY) / Sum(DayCount) does not work properly because not all calendar dates are in the fact table for each employee, so the various charts and pivot tables that have EmployeeName, ALPHA_MGR_NAME etc. as a dimension do not return the correct figure.

Is there an elegant way to isolate the dimCalendar date dimension so that the number of working days is only calculated across the dates selected, rather than it also respond to the dimEmployee dimensions and the dimensions in the fact table.

I know I could do this with Set Analysis similar to this, but if I add a new dimension to a chart or pivot table then I will also need to add it to the working days expression.

Sum({<EmployeeName=,ACTIVITY=,AVAILABILITY>}DayCount)

Thanks

[#2]

Hi James

I've just written a blog post about this.

Qlik-Fit - Dashboards for healthcare informatics: Set analysis that ignores all selections except a ...

In a nutshell you can use combinations of the system fields, variables and string functions to generate lists of fields that you wish to include / exclude from set analysis.

Use this expression in a variable to list all your fields you wish to ignore:

vIgnoreFields=concat({<[$Table]-={'DimCalendat'}>} distinct [$Field] & '='   ,   ','   )

This will produce a string list of fields that are not in your calendar

Rowno=,Capacity=,.... etc

Then execute the variable in your chart expression

=sum({<$(vIgnoreFields)>} DayCount)

Regards,

Erica