How do I store variable name in an inline table without being evaluated?

Mon, 17 Oct 2016 21:13:25 GMT

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.

Re: How do I store variable name in an inline table without being evaluated?

Mon, 12 Dec 2016 19:01:49 GMT

My proposed solution worked, but I had to also create a loop that stored all of my dimensions into a set analysis variable. That way I could control what I am ignoring in each chart. It is a pain to implement this, but I saw no other way.

topic How do I store variable name in an inline table without being evaluated? in QlikView

How do I store variable name in an inline table without being evaluated?

Re: How do I store variable name in an inline table without being evaluated?