1 Reply Latest reply: Jul 10, 2014 6:52 PM by Erica Whalley RSS

    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