YTD Distinct Count and YTD-YoY Distinct Count

Question & Answer
, ,
1

Hi all,

I want to calculate YTD distinct count of GLs used for shopping (by the customers) and YTD-YoY% for the same but I am not able to perform this calculation. I tried below mentioned two calculations but am not getting the desired result.

  • GLs shopped: distinct_count({gl_product_group_desc})
  • YTD - Disctinct GLs shopped - v1: periodToDateSumOverTime(distinct_count({gl_product_group_desc}), {reporting_date}, YEAR)
  • YTD - Disctinct GLs shopped - v2:
    periodToDateSumOverTime( runningSum( sumOver( distinct_count({gl_product_group_desc}),[{reporting_date},{gifting_experience}] ) , [{reporting_date} ASC],[truncDate('MM',{reporting_date}),{gifting_experience}] ), {reporting_date},YEAR )

I’ve created a sample dashboard using Arena (YTD - Distinct GLs Shopped) using a sample dataset.

Looking at the screenshot, it seems like the YTD field is just doing a summation of the “GLs shopped” every month. For instance, the YTD value for Apr-2024 is “Mar-2024 + Apr-2024” and the YTD value for May-2024 is “Mar-2024 + Apr-2024 + May-2024” but it’s not giving me the YTD Distinct count of GLs because there are GLs which are common across months (e.g. see the second screenshot for Baby Registry), so those GLs should be counted only once while calculating YTD value. Please take a look and advise what we can do further.

Currently I am not filtering data in the dashboard using filters but these calculations should show the correct values when we change filters (e.g. Date, Marketplace etc.) in the dashboard.

Screenshot 2024-07-30 at 14.38.24
Screenshot 2024-07-30 at 14.38.241438Ă—450 23.2 KB

Screenshot 2024-07-30 at 14.48.40
Screenshot 2024-07-30 at 14.48.40696Ă—872 16.8 KB

2

Hello @chabbils !

Cloud you clarify a few points so that I can better understand your question?

  1. Without revealing any proprietary information can you describe more about the GL field and why it should only be counted once per product over the three month interval?

  2. Can you clarify this from your old post: "For instance, the YTD value for Apr-2024 is “Mar-2024 + Apr-2024” and the YTD value for May-2024 is “Mar-2024 + Apr-2024 + May-2024” but it’s not giving me the YTD Distinct count of GLs because there are GLs which are common across months (e.g. see the second screenshot for Baby Registry), so those GLs should be counted only once while calculating YTD value". Do you mean that even if a GL is recurring it shouldn’t be counted 3 time? For example, for YTD of a single recurring sale from customer A every month, over a single year I would expect that YTD I have made 12 sales from customer A, but what you described above is saying that YTD I have 1 recurring event?

  3. Is GL a running total? So for example, GL for apparel is 1 in Mar, 0 in April, and 0 in May so YTD is 1?

1 Like
3

Hi @duncan, PFB the required information and let me know if you need any further details:

  1. We want to know how many different/distinct GLs people are shopping from at Quarterly/Yearly level for each gifting experience. That’s why we want the GLs to be counted once at Quarterly/Yearly level (keeping other granularities as it is e.g. Marketplace, Product Family etc.).

  2. You can ignore my example (as I’ve data starting Mar-2024 and not from Jan-2024 but I’ll backfill my dashboard later on). Basically I want to count a GL only once to get the YTD value. For instance, for YTD value in Jan-2024, the GLs shopped in Jan should be counted once and for YTD value in Feb-2024, the GLs shopped in Jan and Feb should be counted once and for YTD value in Mar-2024, the GLs shopped in Jan and Feb and Mar should be counted once (i.e. even if a customer purchased from a GLs in all 3 months, it should be counted only once).

  3. GL is a String field (not a numerical field or running total, you can check this in Arena dashboard as well). I am creating calculated filed(s) to get YTD Distinct GLs shopped from.

4

Hi @duncan, did you get a chance to look into this a little deeper?

5

Hi @duncan, @DylanM, can you please take a look at this issue?

6

Hello @chabbils, I worked through a couple of different calculated fields to try and resolve this. I think there are going to be limitation issues due to the fact that we are trying to run these distinct GL counts across various time periods, i.e. Year and Quarter, and then applying a running total calculation. I was able to create 2 different options to try and resolve this here YTD - Distinct GLs Shopped - Copy

The first chart will show a running total that is only counting distinct GLs per Quarter but it will continue to run the total across data in the table. The 2nd chart will actually return a running total, partitioned by quarter (so it restarts for each new quarter), and returns the distinct count of GLs that appeared once per Quarter.

Due to limitations of the type of calculated fields needed and the requirement to have all of the calculated fields present in the visual, these were the best 2 options I was able to create. This is going to be the closest to your expected output that I will be able to get, so I hope this helps.

2 Likes