12 Replies Latest reply: Dec 13, 2016 3:14 PM by Sunny Talwar RSS

    Chart Funcationality: How can I ignore evaluation of second dimension (for an expression)?

    Albert Flecha

      Hello,

       

      I am trying to calculate the number of business days by month. The tricky part is getting Qlik to evaluate this calculation while ignoring the second dimension.

       

      The expression for # Business Days is,       sum( {<BusinessDay={'YES'}, Task= >} EventDayCount)

      *EventDayCount is part of my calendar table which is linked to my FACT table

      *Task is part of my Task table which is linked to my FACT table

      *FACT table contains the records of work and it refers to the calendar table and Task table

       

      This is what the current output looks like,

      MonthTask# Business Days
      Jan-2016Data Processing20
      Jan-2016Meetings10
      Jan-2016Client Support7
      Feb-2016Data Processing20
      Feb-2016Meetings6
      Feb-2016Client Support11

       

      This is my desired output,

      MonthTask# Business Days
      Jan-2016Data Processing20
      Jan-2016Meetings20
      Jan-2016Client Support20
      Feb-2016Data Processing20
      Feb-2016Meetings20
      Feb-2016Client Support20

       

      Even after using set analysis in my expression, Qlik is still evaluating the second dimension and showing me how many business days I have data for each task.

       

      I tried using the aggr function but I could not figure out how to structure it so it will evaluate the first dimension and properly populate the number of days in each row. I also tried precalculating this value in a variable first and then pulling it in, but I have the same problem with Qlik evaluating the second dimension.

       

      Is it possible to get my desired output?