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



      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-2016Client Support7
      Feb-2016Data Processing20
      Feb-2016Client Support11


      This is my desired output,

      MonthTask# Business Days
      Jan-2016Data Processing20
      Jan-2016Client Support20
      Feb-2016Data Processing20
      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?