I hope someone can help with this.
We have a field sak, which represent the caseId, which is uniqe. We have many traces/records with the same case ID representing each step the uniqe case is beeing processed in a business flow.
In this case, we want to create a dashboard with a table that presents the total number of cases started, and how many of these were started manual or digital.
We are first doing a dedup of the caseId, so we ensure that we do not count the same caseId twice. However, we are using a lot of fields from the record to filter/determine whether a case has been started manual and digital (channel, st_felt, etc.), and not all the records will hold these values. This means, that when we do the dedup, it randomly chooses the record which may or may not contain the value of the field we are using to filter on manual/digital started cases. This means, if you choose a timeline back in time, the numbers will change everytime you click refresh depending on which records ir chooses.
Another issue is when verifying the data with a distictCount of the caseID, these numbers also change when refreshing. This should not be possible, since the number of uniqe caseId's should be consistent nomatter which record of the caseId it chooses.
Does anyone have a suggestion on how to solve this with another approach, maybe without dedup?
Here is the query:
fetch bizevents
| filter event.category == "PMKREDITT"
// WS_TO determines the step the case is currently in, in this case the first step
| filter WS_TO == "001" or WS_FROM == "24"
| filter pi_tilstand >= "2"
| dedup sak
| fieldsAdd date = formatTimestamp(timestamp, format:"MM-dd-yyyy"),
dayOfWeek = formatTimestamp(timestamp, format: "E")
| summarize {countDistinct(sak),StartedAvKundeDigitalCount = countIf(channel == "NETTBANK" and st_felt == "MODUS" and st_verdi == "REFIN_INCRLOAN") , StartedAvRadgiverDigitalCount = countIf(channel =="KONTORNETT" and CONTEXT == "CUSTOMER"), StartedAvRadgiverManualCount = countIf(channel =="KONTORNETT" and (CONTEXT == "MANUAL") or matchesPhrase(sak_sist_endret_av, "AB*") or matchesPhrase(sak_sist_endret_av, "AD*")) }, by: {date, dayOfWeek}
| fieldsAdd `Total cases` = StartedAvKundeDigitalCount + StartedAvRadgiverDigitalCount + StartedAvRadgiverManualCount
| fieldsAdd `Antall saker med digital inngang` = StartedAvKundeDigitalCount + StartedAvRadgiverDigitalCount
| fieldsAdd `Snitt andel saker med digital inngang` = toDouble(`Antall saker med digital inngang`) / `Total cases` * 100
| sort date asc
| fieldsRename `Date`=date, `Weekday`=dayOfWeek
