Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have the below sample data in Qlik Sense.
Business User | Type | Case Dimension Sequence ID | Transaction Date | Next Transaction Date | Case Status Code | Case Close Date | Case Transaction Sequence Number | Minimum Task Start Date |
ABC | Task | 135 | 10/17/2017 | 01/01/2100 | A | 10/19/2017 | 12 | 10/02/2017 |
ABC | Task | 135 | 10/17/2017 | 10/17/2017 | A | 10/19/2017 | 11 | 10/02/2017 |
ABC | Task | 135 | 10/13/2017 | 10/17/2017 | A | 10/19/2017 | 10 | 10/02/2017 |
ABC | Task | 135 | 10/13/2017 | 10/13/2017 | A | 10/19/2017 | 9 | 10/02/2017 |
ABC | Task | 135 | 10/11/2017 | 10/13/2017 | A | 10/19/2017 | 8 | 10/02/2017 |
ABC | Task | 135 | 10/11/2017 | 10/11/2017 | A | 10/19/2017 | 7 | 10/02/2017 |
ABC | Task | 135 | 10/09/2017 | 10/11/2017 | A | 10/19/2017 | 6 | 10/02/2017 |
ABC | Task | 135 | 10/05/2017 | 10/09/2017 | A | 10/19/2017 | 5 | 10/02/2017 |
ABC | Task | 135 | 10/05/2017 | 10/05/2017 | A | 10/19/2017 | 2 | 10/02/2017 |
ABC | Task | 135 | 10/05/2017 | 10/05/2017 | A | 10/19/2017 | 3 | 10/02/2017 |
ABC | Task | 135 | 10/05/2017 | 10/05/2017 | A | 10/19/2017 | 4 | 10/02/2017 |
ABC | Task | 135 | 10/02/2017 | 10/05/2017 | A | 10/19/2017 | 1 | 10/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 User | Measure |
ABC | 11 |
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 User | Type | Case Dimension Sequence ID | Transaction Date | Next Transaction Date | Case Status Code | Case Close Date | Case Transaction Sequence Number | Minimum Task Start Date | Measure | Networkdays([Minimum Task Start Date]+1,$(vEndDate)) |
ABC | Task | 135 | 10/17/2017 | 01/01/2100 | A | 10/19/2017 | 12 | 10/02/2017 | 143 | 11 |
ABC | Task | 135 | 10/17/2017 | 10/17/2017 | A | 10/19/2017 | 11 | 10/02/2017 | 0 | 11 |
ABC | Task | 135 | 10/13/2017 | 10/17/2017 | A | 10/19/2017 | 10 | 10/02/2017 | 0 | 11 |
ABC | Task | 135 | 10/13/2017 | 10/13/2017 | A | 10/19/2017 | 9 | 10/02/2017 | 0 | 11 |
ABC | Task | 135 | 10/11/2017 | 10/13/2017 | A | 10/19/2017 | 8 | 10/02/2017 | 0 | 11 |
ABC | Task | 135 | 10/11/2017 | 10/11/2017 | A | 10/19/2017 | 7 | 10/02/2017 | 0 | 11 |
ABC | Task | 135 | 10/09/2017 | 10/11/2017 | A | 10/19/2017 | 6 | 10/02/2017 | 0 | 11 |
ABC | Task | 135 | 10/05/2017 | 10/09/2017 | A | 10/19/2017 | 5 | 10/02/2017 | 0 | 11 |
ABC | Task | 135 | 10/05/2017 | 10/05/2017 | A | 10/19/2017 | 2 | 10/02/2017 | 0 | 11 |
ABC | Task | 135 | 10/05/2017 | 10/05/2017 | A | 10/19/2017 | 3 | 10/02/2017 | 0 | 11 |
ABC | Task | 135 | 10/05/2017 | 10/05/2017 | A | 10/19/2017 | 4 | 10/02/2017 | 0 | 11 |
ABC | Task | 135 | 10/02/2017 | 10/05/2017 | A | 10/19/2017 | 1 | 10/02/2017 | 0 | 11 |
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