This seems like it should be so simple and yet I cannot figure out the correct qualifier for QlikView so I am turning to all the gurus in the Community... thanks in advance!
I need to show a metric of FTE Capacity. Simply, it should be sum(hours)/sum(month_cap). The user has the ability to select 1+ months. The Data Architect and I have approached this a couple of different ways with no working solution yet. We are trying to avoid adding a snowflake table with the month (reflected as the last day of a month) and hours.
Our FACT table has TRX_DATE which joins to the Calendar table.
1) PREFERRED: Added MONTH_CAP to calendar table. The issue is that the calendar table has an entry for every day and the FISCAL_MONTH_YEAR is in the table as the last day of the month (i.e. 12/31/14 = December 2014). The idea is picking one month should reflect the month_cap for that month (i.e. 160 for December 2014) and multiple months should have the sum of month_cap (I.e 320 for Dec and Jan which have 160 each).
The problem: Sum(month_cap) here results in 160 hours a month * 31 (the number of days in the month).
If Dec and Jan are selected, Sum(distinct month_cap) only results in 160 since Dec and Jan both have 160 hours in the month.
2) Added DAY_WORK_HOURS to calendar table so each day of work is reflected as 8 hours.
The problem here is that FISCAL_MONTH_YEAR is reflected as the last day of the month so sum(DAY_WORK_HRS) only results in 8 hours (or one day).
3) Added an additional subset of data to the FACT table by adding a new column called ‘App’ with a value of MonthCap and the only field populated is MONTH_CAP. The populated MONTH_CAP data by resource was set to null.
The problem here is sum( MONTH_CAP) is now not tied to my other datasets so my dimension filters don’t work.
Any suggestions would be appreciated - especially a simple qualifier for option 1.
