0 Replies Latest reply: Oct 31, 2017 12:20 PM by Rajarshi Guin RSS

    Qlik Sense Measure Calculation Issue

    Rajarshi Guin

      Hi All,

       

      I have the below sample data in Qlik Sense.

       

      Business UserTypeCase Dimension Sequence IDTransaction DateNext Transaction DateCase Status CodeCase Close DateCase Transaction Sequence NumberMinimum Task Start Date
      ABCTask13510/17/201701/01/2100A10/19/20171210/02/2017
      ABCTask13510/17/201710/17/2017A10/19/20171110/02/2017
      ABCTask13510/13/201710/17/2017A10/19/20171010/02/2017
      ABCTask13510/13/201710/13/2017A10/19/2017910/02/2017
      ABCTask13510/11/201710/13/2017A10/19/2017810/02/2017
      ABCTask13510/11/201710/11/2017A10/19/2017710/02/2017
      ABCTask13510/09/201710/11/2017A10/19/2017610/02/2017
      ABCTask13510/05/201710/09/2017A10/19/2017510/02/2017
      ABCTask13510/05/201710/05/2017A10/19/2017210/02/2017
      ABCTask13510/05/201710/05/2017A10/19/2017310/02/2017
      ABCTask13510/05/201710/05/2017A10/19/2017410/02/2017
      ABCTask13510/02/201710/05/2017A10/19/2017110/02/2017

       

      In the above data I have shown only one Case Dimension Sequence ID. But a Business User will have multiple Case Dimesion Sequence ID.

       

      In my dashboard I have Business User as a dimension and in measure I need to show the sum of the networkdays between the Minimum TaskStart Date and a date selected in filter based on the following conditions.

      1. Type=Task

      2. Transaction Date<=Date selected in Filter

      3. Next Transaction Date>Date selected in Filter

      4. Case Close Date>Date selected in Filter

       

      So, in measure I am putting the below formula.

      Sum(

      {<[Type]={'Task'},

      [Transaction Date]={"<=$(=$(vEndDate))"},

      [Next Transaction Date]={">$(=$(vEndDate))"},

      [Case Close Date]={">$(=$(vEndDate))"}>}

      Networkdays([Minimum Task Start Date]+1,$(vEndDate)))

       

      In the variable vEndDate I am putting the date selected in filter.


      So, if I select 10/17/2017 as the date in the filter, in the above example only the first row satisfies all the conditions and I should have the output as Networkdays('10/03/2017,'10/17/2017') which is 11. So the output will be like

        

      Business UserMeasure
      ABC11

       

      But I am getting the value of the measure as 143.

       

      When I calculate the measure for all the rows and columns in the data above I am getting the following.

       

        

      Business UserTypeCase Dimension Sequence IDTransaction DateNext Transaction DateCase Status CodeCase Close DateCase Transaction Sequence NumberMinimum Task Start DateMeasureNetworkdays([Minimum Task Start Date]+1,$(vEndDate))
      ABCTask13510/17/201701/01/2100A10/19/20171210/02/201714311
      ABCTask13510/17/201710/17/2017A10/19/20171110/02/2017011
      ABCTask13510/13/201710/17/2017A10/19/20171010/02/2017011
      ABCTask13510/13/201710/13/2017A10/19/2017910/02/2017011
      ABCTask13510/11/201710/13/2017A10/19/2017810/02/2017011
      ABCTask13510/11/201710/11/2017A10/19/2017710/02/2017011
      ABCTask13510/09/201710/11/2017A10/19/2017610/02/2017011
      ABCTask13510/05/201710/09/2017A10/19/2017510/02/2017011
      ABCTask13510/05/201710/05/2017A10/19/2017210/02/2017011
      ABCTask13510/05/201710/05/2017A10/19/2017310/02/2017011
      ABCTask13510/05/201710/05/2017A10/19/2017410/02/2017011
      ABCTask13510/02/201710/05/2017A10/19/2017110/02/2017011

       

      So, basically the set expression gets applied and the result appears as 0 for all the rows which do not satisfy the conditions. But for the row which satisfies the conditions the network days of all the rows get summed up and appears in that row.

       

      Can anyone please let me know how to resolve this issue.

       

      Thanks,

      Rajarshi