topic Re: Formula Problem in App Development

Formula Problem

GregRyder — Mon, 05 Oct 2026 09:32:02 GMT

I am trying to calculate the month to date sales for last month. I am using an auto calendar.

here is my Formula

Sum({<[TXDATE.autoCalendar.MonthsAgo]={1}, TCODE={'IN'}, [TXDATE.autoCalendar.Month] ={"<=$(=Num(Month(Today()))) "} >} AMOUNT)
-
Sum({<[TXDATE.autoCalendar.MonthsAgo]={1}, TCODE={'CN'}, [TXDATE.autoCalendar.Month] ={"<=$(=Num(Month(Today()))) "} >} AMOUNT)

Re: Formula Problem

GregRyder — Mon, 05 Oct 2026 12:01:20 GMT

This formula is returning the total amount for October (13months Back) and not just for the first 5 days of the month

Sum({<[TXDATE.autoCalendar.MonthsAgo]={13}, TCODE={'IN'}, [TXDATE.autoCalendar.Month] ={"<=$(=Num(Month(Today()))) "} >} AMOUNT)

I am trying to compare this year October vs last year October

Thanks

Re: Formula Problem

Daniel_Castella — Mon, 05 Oct 2026 12:12:35 GMT

Hi @GregRyder

This is because you are never filtering by day in the set analysis. It should be something like this:

Sum({<[TXDATE.autoCalendar.MonthsAgo]={13}, TCODE={'IN'}, [TXDATE.autoCalendar.Day] ={"<=$(=Day(Today())) "} >} AMOUNT)

In this way you are telling to filter for the 13th prior month, code IN, and less or equal to the current day number.

I suppose that the formula set in the first comment works because you don't have future data. Hence, since you only have data until day 5th, it only displays until that day.

Let me know if this works for you.

Kind Regards

Daniel

Re: Formula Problem

GregRyder — Mon, 05 Oct 2026 12:36:55 GMT

Hi Daniel,

Thanks for the reply, but it still returning the full amount for October 2025 and not just for the 1st 5 days of that month

Thanks Greg

Re: Formula Problem

Daniel_Castella — Mon, 05 Oct 2026 13:00:35 GMT

Hi @GregRyder

mmm... I tested the formula that I provided in my end and it works fine. Did you aggregate the data by month in the backend?

Could you, please, provide me with a sample of your raw data to test it?

Thank you very much

Daniel

Re: Formula Problem

GregRyder — Mon, 05 Oct 2026 13:11:49 GMT

Hi Daniel,

I don't aggregate the data by month at all, I have attached two files

Stock.csv

Stochist.csv

TCODE is for transaction code and IN is for Invoice

TXDATE is the date field in my data.

Thnaks

Re: Formula Problem

Daniel_Castella — Mon, 05 Oct 2026 14:33:41 GMT

Hi @GregRyder

Maybe you have an issue with your autocalendar. I have tested with the standard one and I simplified the formula, and I got the correct results.

My autocalendar:

MinMax: Load min(TXDATE) as MinDate, max(TXDATE) as MaxDate resident StocHist; LET vMinDate = Num(Peek('MinDate', 0, 'MinMax')); LET vMaxDate = Num(Peek('MaxDate', 0, 'MinMax')); TempCal: LOAD Date($(vMinDate) + RowNo() - 1) as TempDate AutoGenerate $(vMaxDate) - $(vMinDate) + 1; MasterCalendar: Load Year(TempDate) as Year, Month(TempDate) as Month, Day(TempDate) as Day, Week(TempDate,6) as Week, WeekDay(TempDate) as DayWeekName, TempDate as TXDATE, Num(TempDate) as DateNumber, Num(Month(TempDate)) as MonthNumber, Year(TempDate)&' - '&if(Num(Month(TempDate))<10,'0'&Num(Month(TempDate)), Num(Month(TempDate))) as Year_Month Resident TempCal Order By TempDate; Drop Table TempCal, MinMax;

And the formula used:

Sum({1<Year={"$(=Year(addyears(Today(),-1)))"}, Month={"$(=Month(Today()))"}, Day={"<=$(=day(Today()))"}, TCODE={'IN'} >} AMOUNT)

The result, comparing the formula with the raw data for those days:

Could you, please, share you autocalendar code to compare it?

Thank you very much

Daniel

Re: Formula Problem

GregRyder — Tue, 06 Oct 2026 04:28:23 GMT

Hi Daniel,

My Autocalender is

[autoCalendar]:
DECLARE FIELD DEFINITION Tagged ('$date')
FIELDS
Dual(Year($1), YearStart($1)) AS [Year] Tagged ('$axis', '$year'),
Dual('Q'&Num(Ceil(Num(Month($1))/3)),Num(Ceil(NUM(Month($1))/3),00)) AS [Quarter] Tagged ('$quarter', '$cyclic'),
Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [YearQuarter] Tagged ('$yearquarter', '$qualified'),
Dual('Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [_YearQuarter] Tagged ('$yearquarter', '$hidden', '$simplified'),
Month($1) AS [Month] Tagged ('$month', '$cyclic'),
Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth', '$qualified'),
Dual(Month($1), monthstart($1)) AS [_YearMonth] Tagged ('$axis', '$yearmonth', '$simplified', '$hidden'),
Dual('W'&Num(Week($1),00), Num(Week($1),00)) AS [Week] Tagged ('$weeknumber', '$cyclic'),
Date(Floor($1)) AS [Date] Tagged ('$axis', '$date', '$qualified'),
Date(Floor($1), 'D') AS [_Date] Tagged ('$axis', '$date', '$hidden', '$simplified'),
If (DayNumberOfYear($1) <= DayNumberOfYear(Today()), 1, 0) AS [InYTD] ,
Year(Today())-Year($1) AS [YearsAgo] ,
If (DayNumberOfQuarter($1) <= DayNumberOfQuarter(Today()),1,0) AS [InQTD] ,
4*Year(Today())+Ceil(Month(Today())/3)-4*Year($1)-Ceil(Month($1)/3) AS [QuartersAgo] ,
Ceil(Month(Today())/3)-Ceil(Month($1)/3) AS [QuarterRelNo] ,
If(Day($1)<=Day(Today()),1,0) AS [InMTD] ,
12*Year(Today())+Month(Today())-12*Year($1)-Month($1) AS [MonthsAgo] ,
Month(Today())-Month($1) AS [MonthRelNo] ,
If(WeekDay($1)<=WeekDay(Today()),1,0) AS [InWTD] ,
(WeekStart(Today())-WeekStart($1))/7 AS [WeeksAgo] ,
Week(Today())-Week($1) AS [WeekRelNo] ;

DERIVE FIELDS FROM FIELDS [TXDATE] USING [autoCalendar] ;

Re: Formula Problem

GregRyder — Tue, 06 Oct 2026 06:05:15 GMT

hi Danial,

After a full reload of my app the formula seems to be working fine now, Thanks for the assistance

Re: Formula Problem

Daniel_Castella — Tue, 06 Oct 2026 06:48:20 GMT

Hi @GregRyder

I tried with your autocalendar, but your formula is still giving me the same issue since, as I commented, you are not filtering by Day. I'm not sure why a full reload worked for you, but it shouldn't and the numbers could be misaligned again in the future.

However, I could find why my initial comment was not working. It was because your autocalendar is not generating the field Day. You need to add it in the script as:

Day($1) AS [Day] Tagged ('$day', '$cyclic'),

Then, using the first formula I provided, it works (changing the months ago to 12, not 13):

Sum({1<[TXDATE.autoCalendar.MonthsAgo]={12}, TCODE={'IN'}, [TXDATE.autoCalendar.Day] ={"<=$(=Day(Today())) "} >} AMOUNT)

As you can see in the picture, both of my formulas (1st and 3rd) display the same result and are aligned with the manual selection result (2nd).

Kind Regards

Daniel

Re: Formula Problem

GregRyder — Tue, 06 Oct 2026 07:30:05 GMT

Hi Daniel,

You were correct and the formulae now works as expected, I just one small question now.

The data I have is always 1 day old so as of today the 06/10/206 I only have the data until 05/10/2026 so I want to compare last year October for the same period and not include the data from 06/10/2025.

Hope this makes sense.

Thanks

Greg

Re: Formula Problem

GregRyder — Tue, 06 Oct 2026 08:05:11 GMT

Hi Daniel,

I work it out.

Thanks for all the help