Alternatives to LAG function (non-table calculation functions)

Hi All,

I have a database where I need to calculate the time that a CaseId times go by each Team, the End Date is the Start Date of Next Group, for example, the End Date for group B is 01/01/2023 12:27 (Group C Start Date)

CaseId | Start Date | Group
001 | 01/01/2023 12:23 | A
001 | 01/01/2023 12:25 | B
001 | 01/01/2023 12:27 | C

I can calculate this with LAG function, and using DateDiff I can calculate Case by Case row by row.

But I need to group and calculate the average times for each group and graph, but when I try to do that the message is “table calculation attribute references are missing in field wells”. So, Is there any possibility to use other functions that are not Table Calculation functions?

Thanks

Hello @KLIKIN - Have you tried pushing this calculation at the datasource or dataset level? I believe that would allow you to readily reference this duration column independently as a fact, without having any dependency on any other column. Let me know if this helps!

Hi @sagmukhe, I tried both. I have a similar case and I solved calculating directly in MySQL query, but this data provides from a salesforce connector and I cannot do that.

@KLIKIN - This is indeed tricky then! I am trying to overcome this with some workaround. Since the calculated field “duration” (which will be a difference between the original Start Date field and a calculation involving the lead function) is dependent on Start Date, we need to somehow add that field in the chart. Given that constraint I was trying something like the below snapshot. Added the Start Date under group/color and then de-selected the legend and legend title. Not sure if this is helpful to you, but still thought to share.

1 Like