Count(MonthYear) Returns unexpected Values

[senior_freshmen]

Hello, 

I'm trying to understand why I'm receiving wrong Results with a COUNT() Function.

Following Table is filled with the Source Data: 

ID	DATE	TIME	Content
1	01.01.2022	14:00:36	XXX
2	01.03.2022	09:35:14	YYY
3	05.03.2022	10:50:00	JAH
4	08.03.2022	18:37:00	JJS

This Table I have connected with a Master Calendar: 

QuartersMap:  
MAPPING LOAD   
rowno() as Month,  
'Q' & Ceil (rowno()/3) as Quarter  
AUTOGENERATE (12);  
      
Temp:  
Load  
min(Date) as minDate,  
max(Date) as maxDate  
Resident LessonsLearned;  
      
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));  
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));  
   
TempCalendar:  
LOAD  
$(varMinDate) + Iterno()-1 As Num,  
Date($(varMinDate) + IterNo() - 1) as TempDate AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
      
MasterCalendar:  
Load  
  TempDate AS Date,  
  week(TempDate) As Week,  
  Year(TempDate) As Year,  
  Month(TempDate) As Month,  
  Day(TempDate) As Day,  
  ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,  
  Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,  
  WeekDay(TempDate) as WeekDay, 
  (Date(monthstart(TempDate), 'MMM-YY')) AS MonthYear,
  If(TempDate <= Today() And TempDate >= SetDateYear(Today(), Year(Today())-1), 1, 0) As Last12Months
Resident TempCalendar  
Order By TempDate ASC;  

Drop Table TempCalendar;  
DROP Table Temp;  

So far so good, the Tables are correctly connected via the Date Field: 

But when I Try to count ID Values with MonthYear, I receive unexpected Values: 

Expected: 3 (Like in my Example) 

Actual: 1,17

This is how I configured this Table. 

Has anyone an idea what I am doing wrong? 

[hic · Accepted solution]

You are using a Moving average... 

[hic]

By the way, try the following Master Calendar instead. It does the same, but is faster and cleaner: No mapping table. No variables. No temp tables.

MasterCalendar:
Load
Year(Date) as Year,
Month(Date) as Month,
Day(Date) as Day,
Dual('Q'&Ceil(Month(Date)/3), Ceil(Month(Date)/3)) as Quarter,
Week(Date) as Week,
Week(Weekstart(Date)) & '-' & WeekYear(Date) as WeekYear,
WeekDay(Date) as WeekDay,
Date(Monthstart(Date), 'MMM-YY') as MonthYear,
If(Date<=Today() And Date>AddYears(Today(),-1),1,0) as Last12Months,
Date;
Load // ------------ generate all dates between smallest and largest date ------------
Date( CalendarBegin+Iterno()-1 ) as Date
While CalendarBegin+Iterno()-1<=CalendarEnd;
Load // ------ find smallest and largest value in symbol table; extend to full years ------
YearStart(Min(Fieldvalue('Date',RecNo()))) as CalendarBegin,
YearEnd( Max(Fieldvalue('Date',RecNo()))) as CalendarEnd
Autogenerate FieldValueCount('Date');

[senior_freshmen]

Hi Henric, Thank you for your Help. 

Sometimes you are just looking too long at the same Line without seeing anything....

I also appreciate your Feedback regarding the MasterCalendar. Looking great now!