Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
rajarshi_guin
Contributor III
Contributor III

Qlik Sense Measure Calculation Issue

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

0 Replies