Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Isolating a Dimension for Aggregate Functions

Hi all

I have the following QlikView data model.

tblDailyLA.CAPACITY is the fact value.

DataModel.png

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

1 Reply
Not applicable

Re: Isolating a Dimension for Aggregate Functions

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

Community Browser