QPR ProcessAnalyzer Expression Query Examples: Difference between revisions

From QPR ProcessAnalyzer Wiki
Jump to navigation Jump to search
No edit summary
 
(53 intermediate revisions by 2 users not shown)
Line 1: Line 1:
This page contains example expression queries.
== Single ValueType Examples ==
=== Events grouped by event attributes ===
The following analysis calculates:
The following analysis calculates:
# Number of cases
# Number of cases
Line 45: Line 50:
</pre>
</pre>


For case attributes:
=== Cases grouped by case attributes ===
Same analysis for case attributes:
<pre>
<pre>
{
{
"Root": "Cases",
  "Root": "Cases",
"Dimensions": [
  "Dimensions": [
{
    {
"Name": "Color",
      "Name": "Color",
"Expression": "Color"
      "Expression": "Color"
},
    },
{
    {
"Name": "Category",
      "Name": "Category",
"Expression": "Category"
      "Expression": "Category"
}
    }
],
  ],
"Values": [
  "Values": [
{
    {
"Name": "Count",
      "Name": "Count",
"Expression": "Count(_)"  
      "Expression": "Count(_)"  
},
    },
{
    {
"Name": "Cases",
      "Name": "Cases",
"Expression": "StringJoin(\",\", _.Name)"  
      "Expression": "StringJoin(\",\", _.Name)"  
}
    }
],
    ],
"Ordering": [  
  "Ordering": [  
{
    {
"Name": "Color",
      "Name": "Color",
"Direction": "Ascending"
      "Direction": "Ascending"
},
    },
{
    {
"Name": "Category",
      "Name": "Category",
"Direction": "Ascending"
      "Direction": "Ascending"
}
    }
]
  ]
}
}
</pre>
</pre>


1 dimension selected:
=== Flows Statistics===
 
<pre>
<pre>
{
{
"Root": "Cases",
  "Root": "
"Expressions": [
    Let(\"flowDifferences\", Flows.(_.AverageDuration-_.MedianDuration));
{
    Let(\"minDifference\", Min(flowDifferences));
"Expression": "Color",
    Let(\"maxDifference\", Max(flowDifferences));
"Values": ["Red"]
    Let(\"range\", maxDifference-minDifference);
}
    Flows;",
]
  "RowInitExpression": "
    Let(\"difference\", _.AverageDuration - _.MedianDuration);
    Let(\"caseCount\", Count(_.Cases));
    Let(\"FlowOccurrenceCount\", Count(_.FlowOccurrences));",
  "Values": [
    {
      "name": "Flow",
      "expression": "_.From.if(isnull(_), \"START\", _.Name) + \" -> \" + _.To.if(isnull(_), \"END\", _.Name)"
    },
    {
      "name": "Flow bottleneck index",
      "expression": "(difference - minDifference).TotalSeconds / range.TotalSeconds * 100"
    },
    {
      "name": "Difference between average and median duration (days)",
      "expression": "difference.TotalDays"
    },
    {
      "name": "Average duration (days)",
      "expression": "_.AverageDuration.TotalDays"
    },
    {
      "name": "Median duration (days)",
      "expression": "_.MedianDuration.TotalDays"
    },
    {
      "name": "Duration standard deviation (days)",
      "expression": "_.DurationStandardDeviation.TotalDays"
    },
    {
      "name": "Case count",
      "expression": "caseCount"
    },
    {
      "name": "Flow occurrence count",
      "expression": "FlowOccurrenceCount"
    },
    {
      "name": "Rework %",
      "expression": "(FlowOccurrenceCount / caseCount - 1) * 100"
    }
  ],
  "Ordering": [
    {
      "name": "Flow bottleneck index",
      "Direction": "Descending"
    }
  ]
}
}
</pre>
</pre>


2 dimensions selected:
=== Return only dimension values ===
The following analysis returns a list of all dimensions but doesn't calculate any KPI's:
<pre>
<pre>
{
{
"Root": "Cases",
  "Root": "Cases",
"Expressions": [
  "Dimensions": [
{
    {
"Expression": "Color",
      "Name": "Region",
"Values": ["Red"]
      "Expression": "Region"
},
    }
{
  ],
"Expression": "Category",
  "Values": []
"Values": [2]
}
]
}
}
</pre>
</pre>


3 Grams
=== Return only single KPI value without dimensioning ===
The following analysis calculates a KPI for all filtered data but doesn't slice it to any dimensions:
<pre>
<pre>
{
{
"Root": "Cases",
  "Root": "Cases",
"Dimensions": [
  "Dimensions": [],
{
  "Values": [
"Name": "CaseId",
    {
"Expression": "Name"
      "Name": "Case count",
}
      "Expression": "Count(_)"  
],
    }
"Values": [
  ]
{
"Expression": "
Flatten(
_
.Array(
StringJoin(\"->\",  
Array(\"0\",
GetAt(0, Events).Type.Name,
If(
Count(Events) > 1,
GetAt(1, Events).Type.Name,
\"0\"
)
)
),
StringJoin(\"->\",
Events.Where(!IsNull(NextInCase))
.Array(Type.Name,
NextInCase.Type.Name,
If(
!IsNull(NextInCase.NextInCase),
NextInCase.NextInCase.Type.Name,
\"0\"
)
)
)
)
).Where(_ != \"\")
",
"DimensionOrderExpression": "OrderByValue(_)",
"Type": "Pivot"
}
],
"Ordering": [
{
"Name": "CaseId",
"Direction": "Ascending"
}
]
}
}
</pre>
</pre>


Duration
=== Case duration distribution by hours ===
Following analysis shows case duration distribution by hours (works like the Duration Analysis)
<pre>
<pre>
{
{
"Root": "Cases",
  "Root": "Cases",
"Dimensions": [
    "Dimensions": [
{
      {
"Name": "TotalHours",
        "Name": "Case Duration in Hours",
"Expression": "Duration.TotalHours.Round(0)"
        "Expression": "Duration.TotalHours.Round(0)"
}
      }
],
    ],
"Values": [
    "Values": [
{
      {
"Name": "Count",
        "Name": "Case Count",
"Expression": "Count(_)"  
        "Expression": "Count(_)"  
}
      }
],
    ],
"Ordering": [  
    "Ordering": [  
{
      {
"Name": "TotalHours",
        "Name": "Case Duration in Hours",
"Direction": "Ascending"
        "Direction": "Ascending"
}
    }
]
  ]
}
}


</pre>
</pre>


Monthly Average Case Duration
=== Monthly average case duration ===
The following analysis calculates monthly average case duration. Case start month needs to be a dimension and KPI is the average case duration (also rounding is used).
<pre>
<pre>
{ "Root": "Cases", "Dimensions": [{ "Name": "Start Month", "Expression": "StartTime.Month" }], "Values": [{ "Name": "Average Case Duration in Days", "Expression": "Round(Average(_.Duration.TotalSeconds) / 3600, 0)" }], "Ordering": [{ "Name": "Start Month", "Direction": "Ascending" }]}
{
  "Root": "Cases",
  "Dimensions": [
    {
      "Name": "Start Month",
      "Expression": "StartTime.Month"
    }
  ],
  "Values": [
    {
      "Name": "Average Case Duration in Days",
      "Expression": "Round(Average(_.Duration.TotalSeconds) / 3600, 0)"
    }
  ],
  "Ordering": [
    {
      "Name": "Start Month",
      "Direction": "Ascending"
    }
  ]
}
</pre>
</pre>


Event type ordering
=== Root Causes for Case Attributes ===
The following example shows how to create the Root Causes using the expression language. You need to pass the Comparison parameter which the influence analysis comparison between case sets is based on.
 
<pre>
<pre>
{
{
"Root": "Cases",
  "Root": "
"Dimensions": [
    Let(\"nAllCases\", Count(Cases));
{
    Let(\"nAllSelectedCases\", Count(ComparisonEventLog.Cases));
"Name": "CaseId",
    Let(\"pAllSelectedCases\", nAllSelectedCases / nAllCases);
"Expression": "Name"
    ConcatLevel((CaseAttributes.(Let(\"attribute\", _), Values.[attribute, _])))
}
  ",
],
  "Dimensions": [
"Values": [
    {
{
      "Name": "Attribute",
"Expression": "
      "Expression": "_[0].Name"
Flatten(
    },
_
    {
.Events
      "Name": "Value",
.For(\"i\", IndexInCase + 1, i < Count(Case.Events), i + 1,  
      "Expression": "_[1]"
StringJoin(
    }
\">\",  
  ],
Array(
  "RowInitExpression": "
Type.Name,  
    Let(\"attribute\", _[0][0]);
GetAt(i, Case.Events).Type.Name
    Let(\"selectedAttribute\", (ComparisonEventLog.CaseAttributes.Where(Name == attribute.Name))[0]);
)
    Let(\"value\", _[0][1]);
)
    Let(\"cases\", attribute.CasesHavingValue(value));
)
    Let(\"selectedCases\", selectedAttribute.CasesHavingValue(value));
)
    Let(\"notSelectedCases\", Except(cases, selectedCases));
",
    Let(\"nCases\", Count(cases));
"DimensionOrderExpression": "OrderByValue(_)",
    Let(\"nSelectedCases\", Count(selectedCases));
"Type": "Pivot"
    Let(\"nNotSelectedCases\", Count(notSelectedCases));
}
    Let(\"pSelectedCases\", (nCases == 0) ? 0 : nSelectedCases / nCases);
],
    Let(\"pDiff\", pSelectedCases - pAllSelectedCases);
"Ordering": [  
    Let(\"nContribution\", nCases * pDiff);
{
  ",
"Name": "CaseId",
  "Values": [
"Direction": "Ascending"
    {
}
      "Name": "# Cases",
]
      "Expression": "nCases",
      "Type": "Single"
    },
    {
      "Name": "# Selected",
      "Expression": "nSelectedCases",
      "Type": "Single"
    },
    {
      "Name": "# Not selected",
      "Expression": "nNotSelectedCases",
      "Type": "Single"
    },
    {
      "Name": "Selected %",
      "Expression": "pSelectedCases",
      "Type": "Single"
    },
    {
      "Name": "Difference %",
      "Expression": "pDiff",
      "Type": "Single"
    },
    {
      "Name": "Contribution #",
      "Expression": "nContribution",
      "Type": "Single"
    },
    {
      "Name": "Contribution %",
      "Expression": "(nAllSelectedCases == 0) ? 0 : nContribution / nAllSelectedCases",
      "Type": "Single"
    }
  ],
  "Ordering": [  
    {
      "Name": "Contribution #",
      "Direction": "Descending"
    },
    {
      "Name": "Attribute",
      "Direction": "Ascending"
    },
    {
      "Name": "Value",
      "Direction": "Ascending"
    }
  ]
}
}
</pre>


=== 3 Grams ===
<pre>
{
  "Root": "Cases",
  "Dimensions": [
    {
      "Name": "CaseId",
      "Expression": "Name"
    }
  ],
  "Values": [
    {
      "Expression": "
        Flatten(
          _
          .Array(
            StringJoin(\"->\",
            Array(\"0\",
              GetAt(0, Events).Type.Name,
              If(
                Count(Events) > 1,
                GetAt(1, Events).Type.Name,
                \"0\"
                )
              )
            ),
            StringJoin(\"->\",
              Events.Where(!IsNull(NextInCase))
              .Array(Type.Name,
                NextInCase.Type.Name,
                If(
                  !IsNull(NextInCase.NextInCase),
                  NextInCase.NextInCase.Type.Name,
                  \"0\"
                )
              )
            )
          )
        ).Where(_ != \"\")
      ",
      "DimensionOrderExpression": "OrderByValue(_)",
      "Type": "Pivot"
    }
  ],
  "Ordering": [
    {
      "Name": "CaseId",
      "Direction": "Ascending"
    }
  ]
}
</pre>
</pre>


Event type trend
== Dynamic ValueType Examples ==
 
=== Count of occurred event types by time (Event Type Trends Analysis) ===
The following analysis shows how many different types of events occurred divided to time ranges.
<pre>
<pre>
{
{
"Root": "EventTypes",
  "Root": "EventTypes",
"Dimensions": [
  "Dimensions": [
{
    {
"Name": "Name",
      "Name": "Event Name",
"Expression": "Name"
      "Expression": "Name"
}
    }
],
  ],
"Values": [
  "Values": [
{
    {
"Name": "Visible Count",
      "Name": "Event Count",
"Expression": "GetAt(0, _.Count)"  
      "Expression": "GetAt(0, _.Count)"  
},
    },
{
    {
"NameExpression": "",
      "ValueDimensionExpression": "For(\"i\", 1, i < 12, i + 1, DateTime(2017, i, 1))",
"Name": "DateGroupDim",
      "NameExpression": "\"\" + _.Year + \"-\" + If(_.Month < 10, \"0\", \"\") + _.Month",
"ValueDimensionExpression": "TimeRange(DateTime(2012, 1, 1, 12), DateTime(2012, 1, 1, 13), TimeSpan(0, 0, 10))",
      "Expression": "GetAt(0, Count(
"SelectionExpression": "_.Events.Where(TimeStamp.Round(TimeSpan(0, 0, 10)) == DateGroupDim)",
        _.Events.Where(TimeStamp.Year == ValueDimension.Year &&
"Expression": "GetAt(0, Count(Selection))",
        TimeStamp.Month == ValueDimension.Month)
"Type": "Dynamic"
      ))",
}
      "Type": "Dynamic"
],
    }
"Ordering": [  
  ],
{
  "Ordering": [  
"Name": "Name",
    {
"Direction": "Ascending"
      "Name": "Event Name",
}
      "Direction": "Ascending"
]
    }
  ]
}
}
</pre>


=== Case counts for each case attributes values by time (Profiling Trends Analysis) ===
The following analysis shows case counts for each case attribute values ("Region" in this example) and for each months.
<pre>
{
  "Root": "Cases",
  "Dimensions": [
    {
      "Name": "Region",
      "Expression": "Region"
    }
  ],
  "Values": [
    {
      "ValueDimensionExpression": "For(\"i\", 1, i < 12, i + 1, DateTime(2017, i, 1))",
      "NameExpression": "\"\" + _.Year + \"-\" + If(_.Month < 10, \"0\", \"\") + _.Month",
      "Expression": "GetAt(0, Count(
        _.Events.Where(TimeStamp.Year == ValueDimension.Year && TimeStamp.Month == ValueDimension.Month)
      ))",
      "Type": "Dynamic"
    }
  ],
  "Ordering": [
    {
      "Name": "Region",
      "Direction": "Ascending"
    }
  ]
}
</pre>
Examples for generating different time ranges:
* Quarters: For(\"i\", 1, i <= 4, i + 1, DateTime(2017, i * 3, 1))
* Months: For(\"i\", 1, i <= 12, i + 1, DateTime(2017, i, 1))
* Weeks: Timerange(Datetime(2017,1,2), Datetime(2018,1,1), Timespan(7))
* Days: Timerange(Datetime(2017,1,1), Datetime(2018,1,1), Timespan(1))
* Hours: Timerange(Datetime(2017,1,1), Datetime(2017,1,31), Timespan(0, 1))
* Minutes: Timerange(Datetime(2017,1,1), Datetime(2017,1,2), Timespan(0, 0, 1))
* Seconds: Timerange(Datetime(2017,1,1), Datetime(2017,1,2), Timespan(0, 0, 0, 1))
For equal duration time ranges, the Timespan function is the useful, and for non-equal durations, the For function needs to be used.
== Pivot ValueType Examples ==
=== Event type ordering ===
<pre>
{
  "Root": "Cases",
  "Dimensions": [
    {
      "Name": "CaseId",
      "Expression": "Name"
    }
  ],
  "Values": [
    {
      "Expression": "
        Flatten(
          _
          .Events
          .For(\"i\", IndexInCase + 1, i < Count(Case.Events), i + 1,
            StringJoin(\">\",
              Array(
                Type.Name,
                GetAt(i, Case.Events).Type.Name
              )
            )
          )
        )
      ",
      "DimensionOrderExpression": "OrderByValue(_)",
      "Type": "Pivot"
    }
  ],
  "Ordering": [
    {
      "Name": "CaseId",
      "Direction": "Ascending"
    }
  ]
}
</pre>
=== Repeats ===
<pre>
{
  "Root": "Cases",
  "Dimensions": [
    {
      "Name": "CaseId",
      "Expression": "Name"
    }
  ],
  "Values": [
    {
      "Expression": "
        Flatten(
          _
          .FindRepeats(Events.Type.Name)
          .Repeat(
            Count(GetAt(1, _)) - 1,
            StringJoin(\"->\", GetAt(0, _))
          )
        )
      ",
      "DimensionOrderExpression": "OrderByValue(_)",
      "Type": "Pivot"
    }
  ],
  "Ordering": [
    {
      "Name": "CaseId",
      "Direction": "Ascending"
    }
  ]
}
</pre>
</pre>


Repeats
=== Workload per resource ===
<pre>
<pre>
{
{
"Root": "Cases",
  "Root": "
"Dimensions": [
TimeRange(DateTime(2012,1,1,12,0), DateTime(2012,1,1,13,0), TimeSpan(0, 0, 5))
{
",  
"Name": "CaseId",
  "Dimensions": [
"Expression": "Name"
    {  
}
      "Name": "Time",
],
      "Expression": "_"
"Values": [
    }
{
  ],  
"Expression": "
  "Values": [
Flatten(
    {
_
      "Type": "Pivot",
.FindRepeats(Events.Type.Name)
      "Expression": "
.Repeat(
Def(\"ActiveEventUnitsAt\", \"at\",
Count(GetAt(1, _)) - 1,
  Cases:GetAt(0, Events)
StringJoin(\"->\", GetAt(0, _))
    .RecursiveFind(
)
      NextInCase,
      TimeStamp <= at
      && (IsNull(NextInCase) || (NextInCase.TimeStamp > at))
    )
  .Unit
);
Let(\"CurrentTime\",  
  GetAt(
    0,  
    _
  ),
  Flatten(
    EventLog
    .ActiveEventUnitsAt(
      CurrentTime
    )
  )
)
)
",
"
"DimensionOrderExpression": "OrderByValue(_)",
    }
"Type": "Pivot"
  ]
}
],
"Ordering": [
{
"Name": "CaseId",
"Direction": "Ascending"
}
]
}
}
</pre>
</pre>
[[Category: QPR ProcessAnalyzer]]

Latest revision as of 22:19, 18 April 2023

This page contains example expression queries.

Single ValueType Examples

Events grouped by event attributes

The following analysis calculates:

  1. Number of cases
  2. List of case names
  3. List of distinct case names (single case appears only once in the list)

for each event attribute Color and Category.

{
  "Root": "Events",
  "Dimensions": [
    {
      "Name": "Color",
      "Expression": "Color"
    },
    {
      "Name": "Category",
      "Expression": "Category"
    }
  ],
  "Values": [
    {
      "Name": "Count",
      "Expression": "Count(_)" 
    },
    {
      "Name": "Cases",
      "Expression": "StringJoin(\",\", _.Case.Name)" 
    },
    {
      "Name": "DistinctCases",
      "Expression": "StringJoin(\",\", Distinct(_.Case.Name))" 
    }
  ],
  "Ordering": [ 
    {
      "Name": "Color",
      "Direction": "Ascending"
    },
    {
      "Name": "Category",
      "Direction": "Descending"
    }
  ]
}

Cases grouped by case attributes

Same analysis for case attributes:

{
  "Root": "Cases",
  "Dimensions": [
    {
      "Name": "Color",
      "Expression": "Color"
    },
    {
      "Name": "Category",
      "Expression": "Category"
    }
  ],
  "Values": [
    {
      "Name": "Count",
      "Expression": "Count(_)" 
    },
    {
      "Name": "Cases",
      "Expression": "StringJoin(\",\", _.Name)" 
    }
    ],
  "Ordering": [ 
    {
      "Name": "Color",
      "Direction": "Ascending"
    },
    {
      "Name": "Category",
      "Direction": "Ascending"
    }
  ]
}

Flows Statistics

{
  "Root": "
    Let(\"flowDifferences\", Flows.(_.AverageDuration-_.MedianDuration));
    Let(\"minDifference\", Min(flowDifferences));
    Let(\"maxDifference\", Max(flowDifferences));
    Let(\"range\", maxDifference-minDifference);
    Flows;",
  "RowInitExpression": "
    Let(\"difference\", _.AverageDuration - _.MedianDuration);
    Let(\"caseCount\", Count(_.Cases));
    Let(\"FlowOccurrenceCount\", Count(_.FlowOccurrences));",
  "Values": [
    {
      "name": "Flow",
      "expression": "_.From.if(isnull(_), \"START\", _.Name) + \" -> \" + _.To.if(isnull(_), \"END\", _.Name)"
    },
    {
      "name": "Flow bottleneck index",
      "expression": "(difference - minDifference).TotalSeconds / range.TotalSeconds * 100"
    },
    {
      "name": "Difference between average and median duration (days)",
      "expression": "difference.TotalDays"
    },
    {
      "name": "Average duration (days)",
      "expression": "_.AverageDuration.TotalDays"
    },
    {
      "name": "Median duration (days)",
      "expression": "_.MedianDuration.TotalDays"
    },
    {
      "name": "Duration standard deviation (days)",
      "expression": "_.DurationStandardDeviation.TotalDays"
    },
    {
      "name": "Case count",
      "expression": "caseCount"
    },
    {
      "name": "Flow occurrence count",
      "expression": "FlowOccurrenceCount"
    },
    {
      "name": "Rework %",
      "expression": "(FlowOccurrenceCount / caseCount - 1) * 100"
    }
  ],
  "Ordering": [
    {
      "name": "Flow bottleneck index",
      "Direction": "Descending"
    }
  ]
}

Return only dimension values

The following analysis returns a list of all dimensions but doesn't calculate any KPI's:

{
  "Root": "Cases",
  "Dimensions": [
    {
      "Name": "Region",
      "Expression": "Region"
    }
  ],
  "Values": []
}

Return only single KPI value without dimensioning

The following analysis calculates a KPI for all filtered data but doesn't slice it to any dimensions:

{
  "Root": "Cases",
  "Dimensions": [],
  "Values": [
    {
      "Name": "Case count",
      "Expression": "Count(_)" 
    }
  ]
}

Case duration distribution by hours

Following analysis shows case duration distribution by hours (works like the Duration Analysis)

{
  "Root": "Cases",
    "Dimensions": [
      {
        "Name": "Case Duration in Hours",
        "Expression": "Duration.TotalHours.Round(0)"
      }
    ],
    "Values": [
      {
        "Name": "Case Count",
        "Expression": "Count(_)" 
      }
    ],
    "Ordering": [ 
      {
        "Name": "Case Duration in Hours",
        "Direction": "Ascending"
    }
  ]
}

Monthly average case duration

The following analysis calculates monthly average case duration. Case start month needs to be a dimension and KPI is the average case duration (also rounding is used).

{
  "Root": "Cases",
  "Dimensions": [
    {
      "Name": "Start Month",
      "Expression": "StartTime.Month"
    }
  ],
  "Values": [
    {
      "Name": "Average Case Duration in Days",
      "Expression": "Round(Average(_.Duration.TotalSeconds) / 3600, 0)"
    }
  ],
  "Ordering": [
    {
      "Name": "Start Month",
      "Direction": "Ascending"
    }
  ]
}

Root Causes for Case Attributes

The following example shows how to create the Root Causes using the expression language. You need to pass the Comparison parameter which the influence analysis comparison between case sets is based on.

{
  "Root": "
    Let(\"nAllCases\", Count(Cases));
    Let(\"nAllSelectedCases\", Count(ComparisonEventLog.Cases));
    Let(\"pAllSelectedCases\", nAllSelectedCases / nAllCases);
    ConcatLevel((CaseAttributes.(Let(\"attribute\", _), Values.[attribute, _])))
  ",
  "Dimensions": [
    {
      "Name": "Attribute",
      "Expression": "_[0].Name"
    },
    {
      "Name": "Value",
      "Expression": "_[1]"
    }
  ],
  "RowInitExpression": "
    Let(\"attribute\", _[0][0]);
    Let(\"selectedAttribute\", (ComparisonEventLog.CaseAttributes.Where(Name == attribute.Name))[0]);
    Let(\"value\", _[0][1]);
    Let(\"cases\", attribute.CasesHavingValue(value));
    Let(\"selectedCases\", selectedAttribute.CasesHavingValue(value));
    Let(\"notSelectedCases\", Except(cases, selectedCases));
    Let(\"nCases\", Count(cases));
    Let(\"nSelectedCases\", Count(selectedCases));
    Let(\"nNotSelectedCases\", Count(notSelectedCases));
    Let(\"pSelectedCases\", (nCases == 0) ? 0 : nSelectedCases / nCases);
    Let(\"pDiff\", pSelectedCases - pAllSelectedCases);
    Let(\"nContribution\", nCases * pDiff);
  ",
  "Values": [
    {
      "Name": "# Cases",
      "Expression": "nCases",
      "Type": "Single"
    },
    {
      "Name": "# Selected",
      "Expression": "nSelectedCases",
      "Type": "Single"
    },
    {
      "Name": "# Not selected",
      "Expression": "nNotSelectedCases",
      "Type": "Single"
    },
    {
      "Name": "Selected %",
      "Expression": "pSelectedCases",
      "Type": "Single"
    },
    {
      "Name": "Difference %",
      "Expression": "pDiff",
      "Type": "Single"
    },
    {
      "Name": "Contribution #",
      "Expression": "nContribution",
      "Type": "Single"
    },
    {
      "Name": "Contribution %",
      "Expression": "(nAllSelectedCases == 0) ? 0 : nContribution / nAllSelectedCases",
      "Type": "Single"
    }
  ],
  "Ordering": [ 
    {
      "Name": "Contribution #",
      "Direction": "Descending"
    },
    {
      "Name": "Attribute",
      "Direction": "Ascending"
    },
    {
      "Name": "Value",
      "Direction": "Ascending"
    }
  ]
}

3 Grams

{
  "Root": "Cases",
  "Dimensions": [
    {
      "Name": "CaseId",
      "Expression": "Name"
    }
  ],
  "Values": [
    {
      "Expression": "
        Flatten(
          _
          .Array(
            StringJoin(\"->\", 
            Array(\"0\", 
              GetAt(0, Events).Type.Name, 
              If(
                Count(Events) > 1, 
                GetAt(1, Events).Type.Name, 
                \"0\"
                )
              )
            ),
            StringJoin(\"->\", 
              Events.Where(!IsNull(NextInCase))
              .Array(Type.Name, 
                NextInCase.Type.Name, 
                If(
                  !IsNull(NextInCase.NextInCase), 
                  NextInCase.NextInCase.Type.Name, 
                  \"0\"
                )
              )
            )
          )
        ).Where(_ != \"\")
      ",
      "DimensionOrderExpression": "OrderByValue(_)",
      "Type": "Pivot"
    }
  ],
  "Ordering": [ 
    {
      "Name": "CaseId",
      "Direction": "Ascending"
    }
  ]
}

Dynamic ValueType Examples

Count of occurred event types by time (Event Type Trends Analysis)

The following analysis shows how many different types of events occurred divided to time ranges.

{
  "Root": "EventTypes",
  "Dimensions": [
    {
      "Name": "Event Name",
      "Expression": "Name"
    }
  ],
  "Values": [
    {
      "Name": "Event Count",
      "Expression": "GetAt(0, _.Count)" 
    },
    {
      "ValueDimensionExpression": "For(\"i\", 1, i < 12, i + 1, DateTime(2017, i, 1))",
      "NameExpression": "\"\" + _.Year + \"-\" + If(_.Month < 10, \"0\", \"\") + _.Month",
      "Expression": "GetAt(0, Count(
        _.Events.Where(TimeStamp.Year == ValueDimension.Year &&
        TimeStamp.Month == ValueDimension.Month)
      ))",
      "Type": "Dynamic"
    }
  ],
  "Ordering": [ 
    {
      "Name": "Event Name",
      "Direction": "Ascending"
    }
  ]
}

Case counts for each case attributes values by time (Profiling Trends Analysis)

The following analysis shows case counts for each case attribute values ("Region" in this example) and for each months.

{
  "Root": "Cases",
  "Dimensions": [
    {
      "Name": "Region",
      "Expression": "Region"
    }
  ],
  "Values": [
    {
      "ValueDimensionExpression": "For(\"i\", 1, i < 12, i + 1, DateTime(2017, i, 1))",
      "NameExpression": "\"\" + _.Year + \"-\" + If(_.Month < 10, \"0\", \"\") + _.Month",
      "Expression": "GetAt(0, Count(
        _.Events.Where(TimeStamp.Year == ValueDimension.Year && TimeStamp.Month == ValueDimension.Month)
      ))",
      "Type": "Dynamic"
    }
  ],
  "Ordering": [ 
    {
      "Name": "Region",
      "Direction": "Ascending"
    }
  ]
}

Examples for generating different time ranges:

  • Quarters: For(\"i\", 1, i <= 4, i + 1, DateTime(2017, i * 3, 1))
  • Months: For(\"i\", 1, i <= 12, i + 1, DateTime(2017, i, 1))
  • Weeks: Timerange(Datetime(2017,1,2), Datetime(2018,1,1), Timespan(7))
  • Days: Timerange(Datetime(2017,1,1), Datetime(2018,1,1), Timespan(1))
  • Hours: Timerange(Datetime(2017,1,1), Datetime(2017,1,31), Timespan(0, 1))
  • Minutes: Timerange(Datetime(2017,1,1), Datetime(2017,1,2), Timespan(0, 0, 1))
  • Seconds: Timerange(Datetime(2017,1,1), Datetime(2017,1,2), Timespan(0, 0, 0, 1))

For equal duration time ranges, the Timespan function is the useful, and for non-equal durations, the For function needs to be used.

Pivot ValueType Examples

Event type ordering

{
  "Root": "Cases",
  "Dimensions": [
    {
      "Name": "CaseId",
      "Expression": "Name"
    }
  ],
  "Values": [
    {
      "Expression": "
        Flatten(
          _
          .Events
          .For(\"i\", IndexInCase + 1, i < Count(Case.Events), i + 1, 
            StringJoin(\">\", 
              Array(
                Type.Name, 
                GetAt(i, Case.Events).Type.Name
              )
            )
          )
        )
      ",
      "DimensionOrderExpression": "OrderByValue(_)",
      "Type": "Pivot"
    }
  ],
  "Ordering": [ 
    {
      "Name": "CaseId",
      "Direction": "Ascending"
    }
  ]
}

Repeats

{
  "Root": "Cases",
  "Dimensions": [
    {
      "Name": "CaseId",
      "Expression": "Name"
    }
  ],
  "Values": [
    {
      "Expression": "
        Flatten(
          _
          .FindRepeats(Events.Type.Name)
          .Repeat(
            Count(GetAt(1, _)) - 1, 
            StringJoin(\"->\", GetAt(0, _))
          )
        )
      ",
      "DimensionOrderExpression": "OrderByValue(_)",
      "Type": "Pivot"
    }
  ],
  "Ordering": [ 
    {
      "Name": "CaseId",
      "Direction": "Ascending"
    }
  ]
}

Workload per resource

{
  "Root": "
TimeRange(DateTime(2012,1,1,12,0), DateTime(2012,1,1,13,0), TimeSpan(0, 0, 5))
", 
  "Dimensions": [
    { 
      "Name": "Time",
      "Expression": "_"
    }
  ], 
  "Values": [
    {
      "Type": "Pivot",
      "Expression": "
Def(\"ActiveEventUnitsAt\", \"at\", 
  Cases:GetAt(0, Events)
    .RecursiveFind(
      NextInCase,
      TimeStamp <= at 
      && (IsNull(NextInCase) || (NextInCase.TimeStamp > at))
    )
  .Unit
);
Let(\"CurrentTime\", 
  GetAt(
    0, 
    _
  ), 
  Flatten(
    EventLog
    .ActiveEventUnitsAt(
      CurrentTime
    )
  )
)
"
    }
  ]
}