# How to create a weekly YTD

**URL:** <https://community.amazonquick.com/t/how-to-create-a-weekly-ytd/26398>\
**Category:** Q&A\
**Tags:** calculations, Business-Intelligence-Engineer, quick-sight\
**Created:** [February 25, 2024, 2:06pm UTC](https://community.amazonquick.com/t/how-to-create-a-weekly-ytd/26398 "2024-02-25T14:06:14Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![kofmand](https://avatars.discourse-cdn.com/v4/letter/k/67e7ee/32.png) [@kofmand](https://community.amazonquick.com/u/kofmand)\
**Post date:** [February 25, 2024, 2:06pm UTC](https://community.amazonquick.com/t/how-to-create-a-weekly-ytd/26398/1 "2024-02-25T14:06:14Z")

</div>

I need to create a week-by-week report of a feature usage by customers similar to this:  
(ignore the numbers in the YTD column)

 ![image](https://d1wl4jav5dp96l.cloudfront.net/original/3X/9/a/9afe2a7e65a21a03230c99aa7628b98eef393b6b.png)

My dataset looks like this:

```auto
(sample_date, customer_name, server_id, is_feature_enabled)

```

where `sample_date` is a running date repleated daily even if there is no change in the data.

The visual is constructed as  
`sample_date` aggregated by Week  
`distinct_countIf({customer_name}, {is_feature_enabled}=1)`  
`distinct_count({customer_name})`

My question is: how do I compute a weekly YTD?  
It should be the same as the second column, i.e. the distinct count of customers but for the time range of `year start - row's week`

Note that this is not a running total, as the same customer using the feature during several weeks is counted as one. E.g. theoretically all columns can have same value throughout the year, if all customers use the feature all the time.

Thanks!

---

<div class="post-metadata">

**Author:** ![ErikG](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/erikg/32/14524_2.png) [@ErikG](https://community.amazonquick.com/u/ErikG)\
**Post date:** [February 26, 2024, 6:26am UTC](https://community.amazonquick.com/t/how-to-create-a-weekly-ytd/26398/2 "2024-02-26T06:26:08Z")

</div>

HI @kofmand  
maybe you can have a look at:

> [@Previous Year YTD Aggregation functions](https://community.amazonquick.com/t/previous-year-ytd-aggregation-functions/12839/2):
>
> Hi ChenjingLyu, I hope below solutions will meet your requirements, kindly check these calculations Max Date: maxOver({your date field}, [], PRE\_AGG) Between dateDiff(), addDateTime(), truncDate(), and extract() you can define whatever periods you want (and there are often multiple ways to do each one, these are just some proven examples). Please create Parameter ( Name it as AsOfDate ) by selecting fixed date option, then try below calculations Today: ifelse(dateDiff({order\_date},${As…

or

> [@Partial Period Comparison WTD WOW](https://community.amazonquick.com/t/partial-period-comparison-wtd-wow/17264/4):
>
> Think I may have found a solution WTD Sales = ifelse(dateDiff(truncDate('WK', {date}),${Today},'WK') = 0, {Sales}, NULL) Prior WTD Sales = ifelse(dateDiff(truncDate('WK', {date}),${Today},'WK') = 1 AND extract('WD', {date}) \<= extract('WD', addDateTime(-1, 'DD', now())), {Sales}, NULL) WoW % Diff = (sum({WTD Sales})-sum({Prior WTD Sales}))/sum({Prior WTD Sales})

BR

---

<div class="post-metadata">

**Author:** ![kofmand](https://avatars.discourse-cdn.com/v4/letter/k/67e7ee/32.png) [@kofmand](https://community.amazonquick.com/u/kofmand)\
**Post date:** [February 26, 2024, 5:18pm UTC](https://community.amazonquick.com/t/how-to-create-a-weekly-ytd/26398/3 "2024-02-26T17:18:45Z")

</div>

Thanks @ErikG  
While not exactly a solution but your first link led me to check the `periodToDate*OverTime` which helped me achieve what I need. I’ll post the solution below.

---

<div class="post-metadata">

**Author:** ![kofmand](https://avatars.discourse-cdn.com/v4/letter/k/67e7ee/32.png) [@kofmand](https://community.amazonquick.com/u/kofmand)\
**Post date:** [February 26, 2024, 5:23pm UTC](https://community.amazonquick.com/t/how-to-create-a-weekly-ytd/26398/4 "2024-02-26T17:23:58Z")

</div>

Here’s the solution I found - perhaps it would be helpful to somebody.  
I defined a calculated field **feature user** as  
`ifelse({is_feature_enabled}=1, {customer_name}, NULL)`,

and then the **ytd** field as  
`periodToDateMaxOverTime(distinct_count({feature user}), {sample_day}, YEAR)`