Check for condition against the whole

ArborRose · January 27

Assume the dataset I am given looks like the following (below).

My downtown store was flooded in 2023. Some of my customers may have moved their shopping uptown. I need to filter the list so I only see customers that had "Downtown" amounts in 2022, and also had "Uptown" amounts in 2023.

In the scenario shown, I expect to see only records for James Smith, since that is the only customer who shopped downtown and later shopped uptown.

Can I do this on a card without altering the ETL that produced the dataset?

marcel_luthi · January 29

If you want to show the individual entries then you'd need to modify the BeastModes to use a Fixed function:

LISTAGG(LISTAGG(DISTINCT CASE WHEN Year = 2022 THEN Store END) FIXED BY(Customer Name))

LISTAGG(LISTAGG(DISTINCT CASE WHEN Year = 2023 THEN Store END) FIXED BY (Customer Name))

If you do this, then you can apply the filter an display the individual entries:

ColemenWilson · January 27

Here's how to solve in Magic ETL:

Group By:

2. Join Flags

3. Filter to desired flags

marcel_luthi · January 29

Should be doable if you don't limit your card by Time Range. To not overcomplicate the BeastMode, I'd use 2, one to determine the 2022 stores and one for the 2023 ones.

LISTAGG(DISTINCT CASE WHEN Year = 2022 THEN Store END)

LISTAGG(DISTINCT CASE WHEN Year = 2023 THEN Store END)

Displaying this in a table will yield the following result:

From here you could use a filter on these new fields where 2022 Contains Downtown and 2023 Contains Uptown:

The reason you use contains is so that it'll also include those that might have visited multiple locations on either of the two years.

marcel_luthi · January 29

If you want to show the individual entries then you'd need to modify the BeastModes to use a Fixed function:

LISTAGG(LISTAGG(DISTINCT CASE WHEN Year = 2022 THEN Store END) FIXED BY(Customer Name))

LISTAGG(LISTAGG(DISTINCT CASE WHEN Year = 2023 THEN Store END) FIXED BY (Customer Name))

If you do this, then you can apply the filter an display the individual entries:

ArborRose · January 29

Thanks for the responses.

Regarding the first suggestion, the resulting value for "In Both" results in only "out". The count is always zero. But looking at the data, I can verify there are instances that meet both conditions.

@marcel, I am not familiar with LISTAGG. You say beast mode, as in a formula. But I don't know where you are suggesting these formulas. In a group by formula?

ArborRose · January 29

I am currently validating my numbers. I believe I have a process that is working.

In the first group by, I total 2022 using GROUP_CONCAT.

GROUP_CONCAT(DISTINCT CASE WHEN Year = '2022' THEN Store ELSE NULL END SEPARATOR ', ')

I do the same thing in the next group by (Group By 1), totaling 2023. The output is then aggregated to SUM the amounts by year and month. That gives me the monthly totals for each.

On the other branch, I am totaling by year to get the store totals. If the totals for both locations are greater than zero, I know they were in both.

ArborRose · January 29

My last version didn't quite work either. But the theory still holds. If I separate the group by as shown below. The top one is getting me correct count and amount by month, store. The middle group by is getting me a correct total by year. The bottom group by is identifying the customers common to both.

This gives me numbers I can use, which I have validated as correct. Using the bottom result set as a drop down filter on a dashboard, I can select users with both stores in common. Then display the amounts by month and year. Which solves my need.

The problem with the other way I had it, the totals can't aggregate on the office, because we're comparing concatenated strings like "downtown, downtown, downtown, uptown". That obviously won't aggregate properly. But it does work as an identifier for common stores; similar to the suggestions given.

marcel_luthi · January 29

@ArborRose my suggestion is to create a Beast Mode at the card level as a formula, not as a Tile in ETL since one of your goals was to be able to do this without touching your ETL. While the LISTAGG is not listed is a formula that works and basically just concatenates all entries using a comma by default (hence why I use distinct as is not relevant if you showed 2 or 3 times in 2022 in that store, only the fact that you showed).

When you're in analyzer viewing your card, click on the Add Calculated Field option on the Dateset Panel to the left:

This is where you'll be able to write the formula I shared earlier.

ArborRose · January 29

Thanks for clarifying Marcel. I tried it via the ETL. Domo didn't like the keyword so I used an alternative method to implement the same basic idea.

You are correct, in the scenario I gave y'all. I didn't tell the whole story. My true data involves patients, and I need to count visits.

ColemenWilson · January 30

Ahh gotcha! In my solution I was trying to get a card that would only display rows for the individuals who that meet the criteria and all rows for that individual. Glad you got something that works for you!

ArborRose · January 30

https://community-forums.domo.com/main/discussion/comment/87799#Comment_87799

One row, regardless of the person, will only meet one side of the condition. It's a transaction like a payment.

Each row belongs to a specific store in a specific year. The GROUP_CONCAT or LISTAGG creates a big list of the offices visited. When the values are aggregated the string will have each store visited within. A more efficient method would be to build the store list with distinct stores…but this wasn't necessary in my adhoc scenario.

Thank you for your help.

Check for condition against the whole

Best Answer

Answers

Categories