23 Replies Latest reply: Aug 20, 2018 4:42 PM by Wanyun Yang

# sum up all sales before current month

I have recent 3 years sales data, I'm trying to sum up all sales before August (current month) in these years. My function is:

Sum({<Month={"<(=month(today()))"}>}Sales)

The result is 0, I have no idea what's wrong. Any ideas?

• ###### Re: sum up all sales before current month

Hello,

You can do this two ways.

Option1:

Create a column called year_month in the script like Year &'|'&Month as Year_Month.

Create a flag if( Year_Month < (Year(Today()) &'|'& Month(Today()) ),1, 0) as Year_Month_Flag.

In the chart SUM({<Year_Month_Flag ={1} >} Sales )

Option2: SUM( {< Month = {"=\$(<=Month(Today) )"}, Year ={"=\$(<= Year(Today() ) ) "} >} Sales )

Edited: Typo error. Please use single quotes in &'|'& not double. My bad

• ###### Re: sum up all sales before current month

Hi,

Thanks for help! I'm not able to do it from script. Tried option2, but still 0.

• ###### Re: sum up all sales before current month

Sorry...I can't

• ###### Re: sum up all sales before current month

Is your Month column a numeric field? If it's formatted as Jan, Feb, Mar (...), it won't work.

• ###### Re: sum up all sales before current month

No it's not numeric. Can I use num()?

• ###### Re: sum up all sales before current month

Yeah if in that case try this for Option 1.

IF(Date#(Year_Month, 'YYYY|M') < Date#( ( Year(Today())&'|' & NUM(Month(Today())) ), 'YYYY|M'), 1,0) as Year_Month_Flag;
NUM(Month(Date#(Month, 'MMM'))) as Month,
Year(Date#(Year, 'YYYY')) &'|' &  NUM(Month(Date#(Month, 'MMM')))  AS Year_Month;

Note: You can completely remove the delimiter in all columns too.

• ###### Re: sum up all sales before current month

Hi William,

I tried with if(Month<month(today()),Month), it works. So I think that might not be the reason...

• ###### Re: sum up all sales before current month

Sum({\$<Month={'\$(=Month(Today()))'}>} Sales)

• ###### Re: sum up all sales before current month

This works good.

• ###### Re: sum up all sales before current month

Well, then for test try          Sum({\$<Month={'\$(=Month(AddMonths(Today(), -1)))'}>} Sales)

• ###### Re: sum up all sales before current month

Works good as well.

• ###### Re: sum up all sales before current month

No it's not solved. I think William is only trying to test a comparison.

• ###### Re: sum up all sales before current month

stalwar1

• ###### Re: sum up all sales before current month

May be adding a dollar sign might resolve the issue

Sum({<Month = {"<\$(=Month(Today()))"}>}Sales)

• ###### Re: sum up all sales before current month

no, the result is still 0

• ###### Re: sum up all sales before current month

You replied "this works good" on two comments, which leads everyone to interpret them as working solutions. What worked and what didn't?

• ###### Re: sum up all sales before current month

Because those two comments both only count one month, and my description says count all months before current month, which is seven for now. I thought you guys were just testing numeric comparison with only one month, Andrey also mentioned try his expression for testing, so I replied yes it works.

• ###### Re: sum up all sales before current month

Then please refer to Sunny's comment on creating a numeric month field.

My guess is that using only one month in the set analysis, it can correctly verify the condition ("Aug" = "Aug")

But if you try using <= or <, there's no way for the set expression to know that "Jan" < "Aug", for example.

If you use a numeric month field, it should work.

• ###### Re: sum up all sales before current month

Thanks

• ###### Re: sum up all sales before current month

May be create a new field in the script like this

Num(Month(Date)) as NumMonth

and then try this

Sum({<NumMonth = {"<\$(=Num(Month(Today())))"}>}Sales)

• ###### Re: sum up all sales before current month

Thank you Sunny.