Avg Days Syntax with missing date

[cbaqir]

I have a dashboard that allows the user to select two Phases - eg 2.2 and 3.2 and the idea is to see both the number of tickets that hit both of those phases and also the avg number of days between the phases.

One of the problems I have is that the current code does not allow for the scenario where a ticket hits one phase more than once but does not hit the other phase.

For example, ticket (DEFECT_ID) 527 never entered phase 2.2 at all but did enter phase 3.2 twice - once on 6/8/20 and again on 6/22/20. The calculation is showing 14 days (6/22-6/8) but it should not show up at all because it did not hit phase 2.2. How can I modify the code to still work in this and other situations? QVW attached.

Avg Days expression:

sum(
Aggr(
Max({$<DEFECT_ID = {"=Count(ADJ_GOVERNANCE_PHASE) = GetSelectedCount(ADJ_GOVERNANCE_PHASE)"}>}ADJ_GOVERNANCE_PHASE_START_DATE)- Min({$<DEFECT_ID = {"=Count(ADJ_GOVERNANCE_PHASE) = GetSelectedCount(ADJ_GOVERNANCE_PHASE)"}>}ADJ_GOVERNANCE_PHASE_START_DATE)
,DEFECT_ID))
/Count({$<DEFECT_ID = {"=Count(ADJ_GOVERNANCE_PHASE) = GetSelectedCount(ADJ_GOVERNANCE_PHASE)"}>}DISTINCT DEFECT_ID)

[marcus_sommer · Accepted solution]

One way could be to extract the phase-selections and to separate the single values (maybe within a variable) with something like:

v1: =subfield(concat(distinct ADJ_GOVERNANCE_PHASE, '+'), '+', 1)
v2: =subfield(concat(distinct ADJ_GOVERNANCE_PHASE, '+'), '+', 2)

which might be then queried like:

Max({< ADJ_GOVERNANCE_PHASE = {'$v1)'} > * < ADJ_GOVERNANCE_PHASE = {'$v2)'} > }  
              ADJ_GOVERNANCE_PHASE_START_DATE)

but such an approach doesn't make your already quite complex expressions easier else the opposite especially if there are not only two selected values else n values.

Easier than this  is probably to apply a count-approach similar to your attempt but using distinct like:

count(distinct ADJ_GOVERNANCE_PHASE)

and applying the check not within the set analysis else using it as a kind of boolean flag, this may look like:

sum(aggr(max()-min(), Dim)) * -(count(distinct ADJ_GOVERNANCE_PHASE)=2)

Beside this I could imagine that some logic might be better transferred into the script to simplify the UI and to reduce/avoid the need of using so many aggr-constructs. In regard to your selections-requirement the And-mode  might be an option.

- Marcus

[cbaqir]

May something like if count(distinct ADJ_GOVERNANCE_PHASE)=2, then exp... thanks Marcus. I will play with it. 

[cbaqir]

I wonder if I could use =if(aggr(count(distinct ADJ_GOVERNANCE_PHASE),DEFECT_ID)=2,DEFECT_ID) as a Dimension.