topic Re: Count(MonthYear) Returns unexpected Values in App Development

Count(MonthYear) Returns unexpected Values

senior_freshmen — Wed, 02 Mar 2022 08:07:49 GMT

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?

Re: Count(MonthYear) Returns unexpected Values

hic — Wed, 02 Mar 2022 08:58:28 GMT

You are using a Moving average...

Re: Count(MonthYear) Returns unexpected Values

hic — Wed, 02 Mar 2022 09:28:46 GMT

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');

Re: Count(MonthYear) Returns unexpected Values

senior_freshmen — Wed, 02 Mar 2022 09:43:12 GMT

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!