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

[GregRyder]

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] ;

[GregRyder]

hi Danial,

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

[Daniel_Castella · Accepted solution]

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