12 Columns for 12 rolling months

[contributor_H]

I'm struggling with the following

Having two columns (date and quantity). I want to create 12 rolling columns for summing the quantity for each month for this year,
for example
1st column let's name it current_month_quantity would be the summation of quantity from 1'st of month to today
2nd column named month_2_quantity would be the summation of the quantity for the previous month
3rd column named month_3_quantity would be the summation of the quantity for the previous-previous month
.
.
.
till 12 month

I've tried something as follows but it doesn't seem to work
Sum({<date = {">=$(=MonthStart(Max(date))) <=$(=Max(date))"}>} quantity) 
Sum({<date = {">=(=MonthStart(Max(date),−1))<=(=MonthEnd(Max(date), -1))"}>} quantity) 

any help is appreciated

[rubenmarin]

Hi, where are you using this? Because 'as' is sused to give a field name, and that's used in the script, meanwhile you are also using set analysis, that is used on chart expressions.

If it's on chart remove the 'as part.

I think it's just atypo but the second expressions lacks the $:

Sum({<date = {">=$(=MonthStart(Max(date),−1))<=$(=MonthEnd(Max(date), -1))"}>} quantity)

Also note that monthend will return also the time, maybe you need to add a floor, or use a monthstart of the next month and an '<' instead of '<=':

- Sum({<date = {">=$(=MonthStart(Max(date),−1))<=$(=Floor(MonthEnd(Max(date), -1)))"}>} quantity)

- Sum({<date = {">=$(=MonthStart(Max(date),−1))<$(=MonthStart(Max(date)))"}>} quantity)

[contributor_H]

Thanks for the reply,

I'm using it in table expression, I tried all your solutions but they don't seem to work, it seems they are all giving  me the total for all months, not the previous month.

I've tried something  as below, it's working but when the month falls in the previous year it doesn't work

sum( if( Month(date) = Month(Today()-1) and Year(date) = Year(Today()-1) , TotalQuantity))

[rubenmarin]

Hi, working with dates can be tricky because of date formats. As a quick test you can try to add date format:

 Sum({<date = {">=$(=Date(MonthStart(Max(date),−1)))<$(=Date(MonthStart(Max(date))))"}>} quantity)

In the expression editor there is a grey box below where the expressions are expanded, so you can see the value returned by the $-expansion part.

Check the values returned there to confirm that it returns the expected value.

You can also first try with fixed values and when it works make them dynamic, so try something like:

 Sum({<date = {">=01/09/2023<01/10/2023"}>} quantity)

[contributor_H]

It shows below

[rubenmarin]

That means that something in the fisrt part of the comparison is wrong. Check again if it's the same that I posted because I dont see anything wrong in the expression.

[contributor_H]

it seems the date is working but the result is wrong, here in expression output says  greater than 1-oct.

but the result in the table shows total

[contributor_H]

I also tried this, which the date condition brings only September as it's shown

and another one for Aug

but both sep and aug are giving me the same result, the total

the expressions are:

sep:

Sum({<Date={">=$(=MonthStart(Max(DateLastEditted_Default), -1))<=$(=MonthEnd(Max(DateLastEditted_Default), -1))"}>} TotalQuantity)

Aug:

Sum({<Date={">=$(=MonthStart(Max(DateLastEditted_Default), -2))<=$(=MonthEnd(Max(DateLastEditted_Default), -2))"}>} TotalQuantity)

[rubenmarin]

Hi, note that Date field name is un blue, meaning it doesn't found a field with that name, field names are shown in orange. Check the name f the date field in the data model

[contributor_H]

Hi Ruben,

please note I'm already loading the date in the load editor, find the below line:

Date(DateLastEditted, 'MM/DD/YYYY') as DateLastEditted_Default,