11 Replies Latest reply: Aug 31, 2012 9:58 AM by Torbjörn Ungvall RSS

    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

       


        • Re: Time in SET
          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))

          • Re: Time in SET

            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

              • Re: Time in SET
                Jagan Nalla

                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

                  • Re: Time in SET

                    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

                      • Re: Time in SET
                        Jagan Nalla

                        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

                          • Re: Time in SET

                            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

                              • Re: Time in SET
                                Chris Cammers

                                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