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] My dataset looks like this: (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!

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)

HI @kofmand maybe you can have a look at: [image] Previous Year YTD Aggregation functions Question & Answer 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 [image] Partial Period Comparison WTD WOW Question & Answer 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

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.

How to create a weekly YTD

Q&A

ErikG February 26, 2024, 6:26am 2

HI @kofmand
maybe you can have a look at:

Topic		Replies	Views
Count Unique Values YTD - New calculation for every year Q&A analysis , quick-sight , calculations	7	1351	August 16, 2023
How to divide weekly sales by annual customers? Q&A quick-sight , calculations , Business-Intelligence-Engineer	4	283	November 8, 2023
YTD Distinct Count & YTD-YoY% Distinct Count Q&A quick-sight , calculations , pivot-table , Business-Intelligence-Engineer	6	161	August 8, 2024
Rolling 4 week average formula based on distinct_count Q&A quick-sight , calculations	5	996	February 28, 2023
Period Over Period and Other Date Comparison Calculations (YoY, MoM, YTD, Rolling 90 days, etc) Technical Articles yoy , date , period-over-period , quick-sight , calculations , Business-Intelligence-Engineer	103	39021	November 24, 2025

How to create a weekly YTD

Related topics