Calculating the average value of the difference between two sequential dates

Hi,
I have a set of events , each event has a type attribute.
In each group of a certain type of event the events are made sequentially one by one and can be ordered by the event_date.
I would like to know in average how often does each type of event occurs.
The steps I’ve made:
1, Calculated field for the sequential next event date = lag(min(date), [date DESC],1,[event_type])
2. Calculated field for the day difference between current event and the sequential next event date = dateDiff(max(date),lag(min(date), [date DESC],1,[event_type]),‘DD’)
3. I try to create a table aggregated by event_type and choose the calculated day difference as a measure.
The measure aggregation is ‘Custom’ and can’t be changed and i get the following error_code= VISUAL_CALC_REFERENCE_MISSING
Can you assist?
Thanks
Tal

Hi @tal,
That error code is caused by not incorporating a field in your visual that the calculation is relying on. Since you’re using a table visual, what if you tried adding the ‘date’ field and then hide it?

Hi @tal,
Following up here as it’s been awhile since we last heard from you; did you have any additional questions regarding your case or were you able to find a work around?

If we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you!

Hi Brett, and thank you for your answer.
I tried adding the date and hiding it BUT the problem is that the row is still duplicated and i would like to see aggregated data for the list of distinct Type.
After adding the date for each type i see row for each date and i would like to see a row for each Type.

Hi @tal,
Apologies that I missed your last response, are you still encountering the same issues or were you able to find a work around?
If still encountering, could you please included some screenshots of your visual so that I can see how this looks vs. how you’d like it to look?

Hi Brett,
yes. it is still occurring. See attached screenshots.
I was able to get the value i am interested in , which is the avg days difference between each 2 sequential days, but i can only get the value when i add the date value .
The steps I’ve made:
1, Calculated field for the sequential next event date = lag(min(date), [date DESC],1,[event_type])
2. Calculated field for the day difference between current event and the sequential next event date = dateDiff(max(date),lag(min(date), [date DESC],1,[event_type]),‘DD’)
3. i added a calculated field for the average : avgOver(daysFromPrevPayment,[vendor])
4. I try to create a table aggregated by event_type and choose the calculated day difference as a measure.
The measure aggregation is ‘Custom’ and can’t be changed and i get the following error_code= VISUAL_CALC_REFERENCE_MISSING


Hello @tal

That error means that the table visual needs a field from your calculation to display data. If you are having trouble with working around this issue, I have found that the easiest thing to do is make aggregations on my dataset using SQL during ingestion from my data source.

Hello @tal

Were you able to find a solution or are you still working on this issue?

Hello @tal

Since we haven’t heard back from you in a while this topic will be archived.

If you still need help with this issue or have a new question relating to this please post a new topic at the top of the community with this post linked in your question.