Formula Problem

[GregRyder]

Hi

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) 

[GregRyder]

Hi

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

[Daniel_Castella]

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

[GregRyder]

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

[Daniel_Castella]

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

[GregRyder]

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

[Daniel_Castella]

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