Time in SET

[#1]

Hi friends,

It's been to long since I used time in SET, try to avoid that, but now I have to build my time limits in a chart.

I need to see last period - always previous month, this year and for last year. Trying this but can't seem to get it to work:

Sum({$<Year = {$(=Year(Today()))}, Month = {$(=Month(Today())-1)},Year=, Month=>} Amount))

and

Sum({$<Year = {$(=Year(Today())-1)}, Month = {$(=Month(Today())-1)},Year=, Month=>} Amount))

I also need the acumulated amount from yearstart to monthend previous month...

Best regards

Torbjörn Ungvall (@Ungvall)

Senior Business Discovery Manager

Advectas AB

[whiteline]

I think you don't have to "clear" it with ",Year=, Month=" in the end of your set expression.

Sum({$<Year = {$(=Year(Today()))}, Month = {$(=Month(Today())-1)}>} Amount))

[#3]

This can be done by putting the periods into date ranges.

Declare two variables

• vDateNow  =date(addmonths(Date_MonthYear,-1),'MMM-YYYY')
• vDate12 =date(addmonths(Date_MonthYear,-12),'MMM-YYYY')

Then use an expression such as this to find the amount

sum({$< DateMonth = {">=$(#vDate12)<=$(#vDateNow)"} >} Amount)

Hope that helps

[jagannalla]

Hello,

Please check this file. My data looks like this:

Customer	DateField	Month	Amount
A	01/05/2011	May	200
B	02/06/2011	Jun	300
C	03/07/2011	Jul	400
D	04/08/2011	Aug	500
A	01/05/2012	May	600
B	02/06/2012	Jun	700
C	03/07/2012	Jul	800
D	04/08/2012	Aug	900

Dimension as Customer

Current Year- Previous Month:

=Sum({<Year={$(=Year(Today()))},Month={$(=Month(AddMonths(Today(),-1)))}>}Amount)

Previous Year-Previous month:

=Sum({<Year={$(=Year(Today())-1)},Month={$(=Month(AddMonths(Today(),-1)))}>}Amount)

Cheers !!

Jagan

[#5]

Thanks for your time!

Works just fine - could I use the same for acumulated amount (yearstart to monthend previous month) and rolling 12 month, also to previous monthend?

Best regards

Torbjörn Ungvall (@Ungvall)

Senior Business Discovery Manager

Advectas AB

[jagannalla]

Hi,

I didn't get you what you want. Can you explain with small example what you want exactly. I mean How is your data and what you need the output. Did you got any help from my previous post ? So that i can move forward.

Cheers!!

Jagan

[#7]

It works just fine!

Can't give you example for various reasons.

I also need the acumulated amount this year:

Sum of amount from january to july (previous month)

and

The amount for rolling 12 month back:

Sum of amount from august last year to july (previous month)

Hope this helps!

Thanks/Toby

[chriscammers]

What I do for all these time period comparisons is this...

Sum({$<$(='[' & Concat({1<$Table = {"MasterCalendar"}>}$Field,'] = , [' ) & '] = '), MasterDate = {"$(='>=' & MonthStart(Max(MasterDate),-1) & '<=' & AddMonths(Max(MasterDate),-1))"}>} SalesAmount)

So let's break this into parts

• $(='[' & Concat({1<$Table = {"MasterCalendar"}>}$Field,'] = , [' ) & '] = ')

          This part overrides all selections on the MasterCalendar table. The problem I always run into with this stuff is that YTD, MTD, Prior MTD and all other pre selected time slices you can think of need to be sensitive to the "current" selections of the user, if they are not handled properly you end up with charts/columns that go blank when the selections are not compatible. So overriding the selections on the calendar are the first part of preventing incompatible selections.

• MasterDate = {"$(='>=' & MonthStart(Max(MasterDate),-1) & '<=' & AddMonths(Max(MasterDate),-1))"}

          This part makes the selection on the primary date field for your data to match whatever range of dates you want. This example gives prior month to date. so if the user has selected July of 2012 you will see ALL of June if the user selects July 2012 up to July 15 then only June1 through June 15. There are lots of variations you can use based on adding and subtracting months or years from the min max or whatever dates are available.

I hope this helps

Chris

[jagannalla · Accepted solution]

Sum of amount from january to july (previous month):

=Sum({$<DateField={">=$(=Date((YearStart(Today())),'DD/MM/YYYY'))<=$(=Date(MonthEnd(AddMonths(Today(),-1)),'DD/MM/YYYY'))"}>} Amount)

Sum of amount from august last year to july (previous month):

Sum({$<DateField={">=$(=Date(MonthStart(AddMonths(Today(),-12)),'DD/MM/YYYY'))<=$(=Date(MonthEnd(AddMonths(Today(),-1)),'DD/MM/YYYY'))"}>} Amount)

Cheers!!

Jagan

[#10]

Thanks a lot! have a great weekend...