Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Time in SET

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


1 Solution

Accepted Solutions
jagannalla
Partner - Specialist III
Partner - Specialist III

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

View solution in original post

11 Replies
whiteline
Master II
Master II

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))

Not applicable
Author

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
Partner - Specialist III
Partner - Specialist III

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

Not applicable
Author

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
Partner - Specialist III
Partner - Specialist III

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

Not applicable
Author

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
Partner - Specialist
Partner - Specialist

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
Partner - Specialist III
Partner - Specialist III

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

Not applicable
Author

Thanks a lot! have a great weekend...