How to fix mismatched aggregation issue

Question & Answer
,
1

I’m trying to find the date when customer leave by labeling who doesn’t have transaction the next day. Currently, I tried ifelse(isNull(periodOverPeriodLastValue(). However, the latest transaction (4.1) will also be marked since there is no data on the next day(4.2) for sure. If I add another condition to filter the date using 'AND date <>last date of the dataset(calculated using maxOver), mismatched aggregation issue shows up. How can I fix this problem? Thanks in advance

2

Hi Vicky, sounds to me you shouldn’t use the isNull(periodOverPeriodLastValue(), this function would only find out if value of last period is null or not, but is not going to tell you whether period after that have values or not.
You can try to get the latest date of record for each customer by 1) filtering on record isnotnull, and 2) filter on denseRank([max({Order Date}) DESC],[Customer])=1
Please let me know if it helps. Thanks!

3

Hi emily, thanks for your solution! If I use max(order date) however, I could not find out those return customers (e.g. someone leave in January and come back in March). Because the denseRank(max()) will rank all of the order date of a customer, and give me the latest one. Do you have any suggestion for this issue?

4

Book1 - Excel 4_18_2022 3_46_47 PM
Book1 - Excel 4_18_2022 3_46_47 PM814×435 23.2 KB

Here’s an example dataset and the column in yellow would be something that I want to create.

5

so you essentially want to find out the begining (column E) and end (clumn D) of a continuous period of days for each company? say in your case if there is no order on 2/3, there will be no record for 2/3? if one order showing up in 2/4, your column D and E will be (0,1)?

6

@emilyzhu, Yes! That’s what I mean! If there’s no order, then there will be no record for that company.

So currently, I’m using periodOverPeriodLastValue(min({sale}),{order date},DAY,-1) to find out if a company doesn’t have order the next day. Then filtering on isnull record to get the column D.

However, after calculating with this method, I can’t create any other visuals like bar graph to showcase how many companies leave in a particular month.

7

You will need to transfer this logic into SQL. If you have it based of calculated fields then you will need to reference any of the fields you are using in your calculated fields in the visual as well.