Hi all,
I am trying to figure out how to implement a pretty complex calculation on FTE Effort. In its basic form, it is, Total Effort / # Business Days.
The problem is that sometimes I want the # of business days to be static (regardless of which filters are selected), and other times, to be evaluated on the fly.
The two scenarios are as follows,
Chart 1 (Evaluate # Business Days outside of the chart) - Assumptions
Year to date business days: 100
$(vBusinessDays) = aggr(sum([BusinessDayCount]), Team) - For each team, count the number of business days.
Chart 1 - Details
**The expression (KPI) is dynamically selected via Listbox from a list of 50+ calculations**
Dimension: Analyst Name
Expression: Total Effort / $(=$(vBusinessDays))
Note: The denominator is in this format because I need Qlik to evaluate the expression outside of the chart. If I did not do it this way, Qlik would evaluate how many business days each analyst showed up in a log (different denominators = inconsistent calculation).
John Doe, 100
Jane Smith, 100
Johnny Depp, 100
Chart 2 - Assumptions
Year to date business days: 100
$(vBusinessDays) = aggr(sum([BusinessDayCount]), Team) - For each team, count the number of business days.
Chart 2 - Details
**The expression (KPI) is dynamically selected via Listbox from a list of 50+ calculations**
Dimension: Month
Expression: Total Effort / $(vBusinessDays)
Note: The denominator is in this format because I need Qlik to evaluate the expression inside of the chart. If I were to use Chart 1's expression, I would have 100 business days for each month.
January, 20
February, 20
March, 20
If I had used Chart 1's calculation:
January, 100
February, 100
March, 100
My proposed solution
I want one version of my calculations in a spreadsheet. I felt that the best way to implement this would be to reference business days in my calculation spreadsheet as [BusinessDayVar].
Then create an inline table with two versions of this calculation:
ID, Type, Calculation
1, Date, $(vBusinessDays)
2, Others, $(=$(vBusinessDays))
Then for each chart, I will use alternate states and/or buttons that point/trigger a specific filter on that table. This is my plan, but I am getting stuck on storing the #2 calculation. Upon import, Qlik is evaluating the expression and storing it a blank.
Chart 1: Date Dimension, Select Type=Date
Expression = Total Effort / [BusinessDayVar]
[BusinessDayVar] = $(vBusinessDays)
Chart 2: Other Dimension, Select Type: Others
Expression = Total Effort / [BusinessDayVar]
[BusinessDayVar] = $(=$(vBusinessDays))
I am open to alternate ways of doing this, but because of the flexibility and the variety of calculations the user could select, it makes this quite difficult to iron out.
Thank you in advance.
