1 Reply Latest reply: Dec 12, 2016 2:01 PM by Albert Flecha RSS

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

    Albert Flecha

      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.