How to show events that happened after a selected one?

0RNGL4DY · December 19

Hello,

How can I show all events individuals have attended after they attended a specific one selected by the dashboard user? Ex. Out of the 200 who attended Conference A in January '24, 100 people attended Conference B in February, 80 Conference C in March, and 110 Conference D in April. I want to show the B, C, D conferences and the names of the people. Table format output is ok, and maybe easiest.

My current dataset has each combination of person x conference as 1 row. I have many additional datapoints around these like person ID, date invited, date attended, etc. Ex:
Person Conference Attended
1. A
1. B
2. A
2. D
3. A
3. B
3. C

I'm aware this could be a complicated set up and can work with it. Can rank functions and/or input variables be of use here? I've been super stuck and would love some help here!

ColemenWilson · December 19

You could use a lead function to get all the conferences attended on the same row in an ETL.
1. Use Rank and Window tile to create lead functions

2. Filter to only rows where Conference Attended = 'A'

Final output:

ColemenWilson · December 19

You could use a lead function to get all the conferences attended on the same row in an ETL.
1. Use Rank and Window tile to create lead functions

2. Filter to only rows where Conference Attended = 'A'

Final output:

Manasi_Panov · December 20

Hi @0RNGL4DY, @ColemenWilson,

This method is effective only if all "Person" entries attended the A conference. If the first conference is B and you wish to check attendance for C, D, etc., an alternative approach is recommended.

I suggest using a Pivot to transform rows into columns, enabling multiple strategies to achieve the desired outcome.

And the result:

Manasi_Panov · December 20

Hi @0RNGL4DY,

This straightforward Python method generates the participation sequence, enabling further data visualization or filtering. Here is the output:

This was achieved with the following simple single line Python script:

#Import the domomagic package into the script

from domomagic import *
import pandas as pd

#read data from inputs into a data frame

input1 = read_dataframe('your_dataset_name_or_the_name_of_the_previous_tile')

#write your script here

input1['Participation_Sequence'] = input1.groupby("Person")["Conference Attended"].transform(lambda x: "->".join(x))

#write a data frame so it's available to the next action

write_dataframe(input1)

ggenovese · December 20

The most simple method of achieving this would be to self join your list of conference attendees to itself, joining on person id. This will create a lot of rows, but I've found that Domo can handle it.

If you want to figure out how many rows it will create in advance you can group by person and count the number of conferences they've attended, then square each number and sum

Example 3^2 + 2^2 + 2^2 = 17

Manasi_Panov · December 20

Hello @ggenovese,

It's super nice how a single problem can be approached in various ways. I'm sure we can think of a few more solutions. It's really about @0RNGL4DY and what will be done later with the Beast Mode and visualizations to determine the most suitable approach for his case.

0RNGL4DY · December 20

Hi All,

Your ideas are so helpful! The layout of the data in the end is something to consider. I might pivot it and do it that way, but save as a separate outflow, as my current output is used across many cards and pages. @ColemenWilson I've tested your solution, and it does work! I added a 4th detail to group the results per person. In the output if there was only 1 more event then "3rd Conference" and "4th Conference" are appropriately blank.

I am checking the requirements with the stakeholders, and if many details are required in the new chart (ex. invite sent, date responded, whether they attended, etc.) I might try the pivot method.

@ggenovese my output is already at 12m rows, so am trying not to multiply the rows, but that mathematical estimation is very helpful.

Thanks again, all. Your input makes me more excited to explore Domo's capabilities : )

How to show events that happened after a selected one?

Best Answer

Answers

Categories