Sum at 1st of every month

[rahulsingh12]

Hi,

I have daily sales data from 1st Jan 2020 to date. I want to create a table where I show the month and sum of sales such that if the month in the table is not equal to month(today()) then it should show sale of 1st of every month else the sale of the max date of the current month.

I wrote the below expression:

IF(TRENDDATEMONTH=MONTH(TODAY()),SUM({<TRENDDATE={"$(vMaxAutoDate)"}>}Final_Cumulative_Cases),

SUM({<TRENDDATE={"=$(=num(monthstart(MAX(TRENDDATE))))"}>}Final_Cumulative_Cases)

)

For the current month of March, it shows correct data for 22nd March 2020 but for other Months of Jan/Feb it is summing up the sales for all days for respective months. Is there a way I can fix the formula.

[kaushiknsolanki · Accepted solution]

HI,

The best way is you create a flag in script to identify the month.

Considering today's date is 23rd Mar 2020.

So your flag expression should be.

If(TRENDDATE = today(),1,

If(TRENDDATE = Monthstart(TRENDDATE),1,0)) as Flag.

Now use this flag in your set analysis.

[alex00321]

You are right! I just expand and put in attached file.

[kaushiknsolanki]

Perfect.

Please mark the post as "Correct Answer"