6 Replies Latest reply on May 24, 2020 9:16 AM by John Nguyen

# How To Aggregate Count Distinct for Previous Years Using Current Date

Hello,

My current set of data contains client id's for the past two years. The client ID's are duplicated since the same user can visit twice - so to generate a count for unique client ID for a specific date I've used this formula

Now being that I have two years worth of data, I would also like to see this UQ value for the previous year, ideally in a field titled "LY UQ" but I can't seem to figure out how to make this happen.

Here's a screenshot of this years date and its YoY equivalent. *Note that there shouldn't be any instance where both the dates of this year or last are shown on screen at the same time

The output I'd like to see is that only May 15th is shown and the LY UQ value is the UQ value for last year which in this case is 5/17/19.

Any help would be greatly appreciated.

Thanks!

• ###### 1. Re: How To Aggregate Count Distinct for Previous Years Using Current Date

John,

I'm not sure if this mockup approximates your true setup,

but please modify the attached workbook to more closely match yours.

There is a parameter to select the date of interest.

Calculated the difference between any date and one year before the date of interest:

ABS(DATEDIFF('day',

[Order Date]))

Last year's date is:

{EXCLUDE [Order Date]:MAX(

IF [Difference in Days to one year ago]=

{EXCLUDE [Order Date] : MIN([Difference in Days to one year ago])}

THEN [Order Date]

END)}

So LY UQ is

{EXCLUDE [Order Date]:COUNTD(

IF [Order Date]=[LY Date]

THEN [Order ID]

END)}

How To Aggregate Count Distinct for Previous Years Using Current Date

• ###### 2. Re: How To Aggregate Count Distinct for Previous Years Using Current Date

Try using LOOKUP(Max(UQ), 1)

• ###### 3. Re: How To Aggregate Count Distinct for Previous Years Using Current Date

John,

I forgot to mention that I didn't put in logic to handle ties,in the event that there isn't data exactly 365 before,

For example, the selected date is 5/23/2019

but the year ago data is only on 5/21/2018

and 5/25/2018.

Both of these will return as the date closest to a

year ago from the selected.

Would you want to only include, for example 5/21/2018?

• ###### 4. Re: How To Aggregate Count Distinct for Previous Years Using Current Date

Hi Swaroop,

The solution you provided worked great when there's a specific date selected within the Selected date parameter. One question, is it possible to update the logic to where there isn't a date selected but instead a range of dates? For example, my data set will include all days in Q2 and will continue to update as each day passes.

So it would look similar to this (note that the LY_UQ values are incorrect in the screenshot)

Let me know if this particular setup would be possible or not

• ###### 5. Re: How To Aggregate Count Distinct for Previous Years Using Current Date

John,

Hmm. I don't think that will work for this paradigm,

because it needs a something set to calculate off of.

Maybe will need to look at table calculations.

A few other questions before commencing in new directions:

-approximately how big is your data set row-wise?

- I ask because there may be some benefit in joining the dataset to itself.

-of what type is your datasource?

-I ask to see if your dataset be amenable to joining to a date scaffold

• ###### 6. Re: How To Aggregate Count Distinct for Previous Years Using Current Date

Hi Swaroop,

No need to explore further - your original answer helped me out a lot. Thanks again for the help!