Define 0 for blank

[StacyCui]

Hi, We have a order table which has two years order data, The structure like below:

SalesID	FrozenDate	ORValue
1001	2022/5/8	100
1001	2022/5/9	50
1001	2022/5/10	30
1001	2022/5/11	70
1002	2022/5/12	200
1002	2022/5/13	40
1005	2022/5/14	0

We need calculate YTD . So I create YTD calendar and concatenate with the master calendar. Master Calendar field 'FrozenDate' could mapping order table to calculate P1 to P12 and YTD order value.

let vMinDate=num(Date('2021-10-01'));
let vMaxDate=num(Date('2023-09-30'));

Master_Calendar_Temp:
LOAD
Date(TempDate) as "FROZENDATE",
Day(TempDate) as 日,
Num(Month(TempDate)) as 月,
if(YearToDate(TempDate,0, 10) or YearToDate(date(num(TempDate)+1),-1, 10),'YTD') as FYTD,
'P'&Month(AddMonths (Date(TempDate,'MM/dd/yyyy'),3))*1 as Fmonth,
Month(TempDate) as 月份,
Year(TempDate) as 年,
if (date(TempDate)>='10/1/2022', 'CY','PY') as FY,
'Q'&Ceil(month(addMonths(TempDate,3)) / 3) as FQ;
LOAD
$(vMinDate) + IterNo() - 1 as TempDate
Autogenerate 1 While ($(vMinDate) + IterNo() - 1) <= $(vMaxDate);

CrossTable(Flag, Month)
LOAD FROZENDATE, FYTD, Fmonth Resident Master_Calendar_Temp;

DROP Field Flag;

But in our business scope, When we select 'YTD', It could show 0 for the customer who doesn't any order until now. Like below screenshot. In other words, All the customers have to map P1 to P12 and YTD, For YTD I have no idea to identify it. How to solve it

[amarkow]

@StacyCui The standard script template (as well as Autocalendar) have a YTD flag generation line.  If a record exists within the YTD, then:

If (DayNumberOfYear($(vDate)) <= DayNumberOfYear(Today()), 1, 0) AS [InYTD]

A company/transaction is a true null if they do not exist within that YTD timeframe, if you want them to appear (even with a 0) you would have to create synthetic records.

Does this help?