14 Replies Latest reply: Aug 26, 2016 10:06 AM by Sunny Talwar RSS

    Point in Time Help

    Buck Master

      Not being cooperative   it should return the prior years sales with the current point in time keys. in this case: ( January thru July of last year. )

      Any ideas?

       

      sum({1<AcctNum = {'Sales'}, Month = {'>=$(=vFirstMonthofYear), <=$(=vCurrentMonth )'}, Year = ${=PriorYear}>} SalesAmount)

       

      Load Script:

        SET vFirstMonthofYear = 'Jan'

        LET CurrentMonth = Month(Today())

        LET PriorYear = Year(YearStart(Today(),-1))


      Thank you

        • Re: Point in Time Help
          Sunny Talwar

          Do you have a date field instead of using Month and Year field? What is the format of date field?

          • Re: Point in Time Help
            Buck Master

            That did not work, our Months are = 'Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec' and our years are normal = 2013,2014,2015,2016

            the solution did not work, any ideas?

              • Re: Point in Time Help
                Sunny Talwar

                Would you be able to share a sample to help you better?

                  • Re: Point in Time Help
                    Buck Master

                    I don't see an attach option?

                    • Re: Point in Time Help
                      Buck Master

                      Using a Table object:

                      Dimension = Customer

                      Measure =  sum({1<SalesMonth = {'>=$(=vFirstMonthofYear), <=$(=vCurrentMonth )'}, SalesYear = ${=PriorYear}>} SalesAmount)

                       

                       

                      SET vFirstMonthofYear = 'Jan'

                      LET CurrentMonth = Month(Today())

                      LET PriorYear = Year(YearStart(Today(),-1))

                       

                      // These are the current rates when the sales was made

                      CurrentCustomers:   

                      Load * Inline [ 

                      Customer, SalesMonth,SalesYear,QTR,SalesAmount,SalesCurrencyType

                      A,Jan, 2014,Q1, 100.00, CAD

                      B,Feb, 2014,Q1, 200.00, CAD

                      C,Mar, 2014,Q1, 300.00, CAD

                      A,Apr, 2014,Q2, 400.00, CAD

                      B,May, 2014,Q2, 500.00, CAD

                      C,Jun, 2014,Q2,    600.00, CAD

                      A,Jul, 2014,Q3, 700.00, CAD

                      B,Aug, 2014,Q3, 800.00, CAD

                      C,Sep, 2014,Q3, 900.00, CAD

                      A,Oct, 2014,Q4, 1000.00, CAD

                      B,Nov, 2014,Q4, 1100.00, CAD

                      C,Dec, 2014,Q4, 1200.00, CAD

                      A,Jan, 2015,Q1, 1130.00, EUR

                      B,Jan, 2015,Q1, 1400.00, GBP

                      C,Feb, 2015,Q1, 1500.00, EUR

                      A,Feb, 2015,Q1, 1600.00, GBP

                      B,Mar, 2015,Q1, 1700.00, DKK

                      C,Mar, 2015,Q1, 1800.00, EUR

                      A,Mar, 2015,Q1, 1900.00, GBP

                      B,Apr, 2015,Q2, 2000.00, EUR

                      C,Apr, 2015,Q2, 2100.00, GBP

                      A,May, 2015,Q2, 2200.00, EUR

                      B,May, 2015,Q2, 2300.00, DKK

                      C,May, 2015,Q2, 2400.00, GBP

                      A,Jun, 2015,Q2, 2500.00, EUR

                      B,Jun, 2015,Q2, 2600.00, GBP

                      C,Jul, 2015,Q3, 2700.00, GBP

                      A,Jul, 2015,Q3, 2800.00, EUR

                      B,Aug, 2015,Q3, 2900.00, EUR

                      C,Aug, 2015,Q3, 3000.00, GBP

                      A,Sep, 2015,Q3, 3100.00, EUR

                      B,Sep, 2015,Q3, 3200.00, GBP

                      C,Oct, 2015,Q4, 3300.00, EUR

                      A,Oct, 2015,Q4, 3400.00, GBP

                      B,Nov, 2015,Q4, 3500.00, EUR

                      C,Nov, 2015,Q4, 3600.00, GBP

                      A,Dec, 2015,Q4, 3700.00, EUR

                      B,Dec, 2015,Q4, 3800.00, GBP

                      C,Jan, 2015,Q1, 3900.00, EUR

                      W,Dec, 2015,Q4, 3700.00, EUR

                      Y,Dec, 2015,Q4, 3800.00, GBP

                      Q,Jan, 2015,Q1, 3900.00, EUR

                      A,Jan, 2016,Q1, 530.00, EUR

                      B,Jan, 2016,Q1, 400.00, GBP

                      C,Feb, 2016,Q1, 500.00, EUR

                      A,Feb, 2016,Q1, 600.00, GBP

                      B,Mar, 2016,Q1, 700.00, DKK

                      C,Mar, 2016,Q1, 800.00, EUR

                      A,Mar, 2016,Q1, 900.00, GBP

                      B,Apr, 2016,Q2, 100.00, EUR

                      C,Apr, 2016,Q2, 100.00, GBP

                      A,May, 2016,Q2, 200.00, EUR

                      B,May, 2016,Q2, 300.00, DKK

                      C,May, 2016,Q2, 400.00, GBP

                      A,Jun, 2016,Q2, 500.00, EUR

                      B,Jun, 2016,Q2, 600.00, GBP

                      C,Jul, 2016,Q3, 700.00, GBP

                      A,Jul, 2016,Q3, 800.00, EUR

                      B,Aug, 2016,Q3, 900.00, EUR

                      C,Aug, 2016,Q3, 700.00, GBP

                      A,Sep, 2016,Q3, 1500.00, EUR

                      B,Sep, 2016,Q3, 1200.00, GBP

                      C,Oct, 2016,Q4, 1300.00, EUR

                      A,Oct, 2016,Q4, 1400.00, GBP

                      B,Nov, 2016,Q4, 1500.00, EUR

                      C,Nov, 2016,Q4, 1600.00, GBP

                      A,Dec, 2016,Q4, 1700.00, EUR

                      B,Dec, 2016,Q4, 1800.00, GBP

                      C,Jan, 2016,Q1, 1900.00, EUR

                      W,Dec, 2016,Q4, 1700.00, EUR

                      Y,Dec, 2016,Q4, 1800.00, GBP

                      Q,Jan, 2016,Q1, 1100.00, EUR];

                  • Re: Point in Time Help
                    Sunny Talwar

                    New Script:

                     

                    LET vEnd = Num(MonthName(AddYears(Today(), -1)));

                    LET vStart = Num(MonthName(YearStart(Today(),-1)));

                     

                    // These are the current rates when the sales was made

                    CurrentCustomers: 

                    Load *,

                      Num(Date#(SalesMonth & '-' & SalesYear, 'MMM-YYYY')) as DateField

                    Inline [

                    Customer, SalesMonth,SalesYear,QTR,SalesAmount,SalesCurrencyType

                    A,Jan, 2014,Q1, 100.00, CAD

                    B,Feb, 2014,Q1, 200.00, CAD

                    C,Mar, 2014,Q1, 300.00, CAD

                    A,Apr, 2014,Q2, 400.00, CAD

                    B,May, 2014,Q2, 500.00, CAD

                    C,Jun, 2014,Q2,    600.00, CAD

                    A,Jul, 2014,Q3, 700.00, CAD

                    B,Aug, 2014,Q3, 800.00, CAD

                    C,Sep, 2014,Q3, 900.00, CAD

                    A,Oct, 2014,Q4, 1000.00, CAD

                    B,Nov, 2014,Q4, 1100.00, CAD

                    C,Dec, 2014,Q4, 1200.00, CAD

                    A,Jan, 2015,Q1, 1130.00, EUR

                    B,Jan, 2015,Q1, 1400.00, GBP

                    C,Feb, 2015,Q1, 1500.00, EUR

                    A,Feb, 2015,Q1, 1600.00, GBP

                    B,Mar, 2015,Q1, 1700.00, DKK

                    C,Mar, 2015,Q1, 1800.00, EUR

                    A,Mar, 2015,Q1, 1900.00, GBP

                    B,Apr, 2015,Q2, 2000.00, EUR

                    C,Apr, 2015,Q2, 2100.00, GBP

                    A,May, 2015,Q2, 2200.00, EUR

                    B,May, 2015,Q2, 2300.00, DKK

                    C,May, 2015,Q2, 2400.00, GBP

                    A,Jun, 2015,Q2, 2500.00, EUR

                    B,Jun, 2015,Q2, 2600.00, GBP

                    C,Jul, 2015,Q3, 2700.00, GBP

                    A,Jul, 2015,Q3, 2800.00, EUR

                    B,Aug, 2015,Q3, 2900.00, EUR

                    C,Aug, 2015,Q3, 3000.00, GBP

                    A,Sep, 2015,Q3, 3100.00, EUR

                    B,Sep, 2015,Q3, 3200.00, GBP

                    C,Oct, 2015,Q4, 3300.00, EUR

                    A,Oct, 2015,Q4, 3400.00, GBP

                    B,Nov, 2015,Q4, 3500.00, EUR

                    C,Nov, 2015,Q4, 3600.00, GBP

                    A,Dec, 2015,Q4, 3700.00, EUR

                    B,Dec, 2015,Q4, 3800.00, GBP

                    C,Jan, 2015,Q1, 3900.00, EUR

                    W,Dec, 2015,Q4, 3700.00, EUR

                    Y,Dec, 2015,Q4, 3800.00, GBP

                    Q,Jan, 2015,Q1, 3900.00, EUR

                    A,Jan, 2016,Q1, 530.00, EUR

                    B,Jan, 2016,Q1, 400.00, GBP

                    C,Feb, 2016,Q1, 500.00, EUR

                    A,Feb, 2016,Q1, 600.00, GBP

                    B,Mar, 2016,Q1, 700.00, DKK

                    C,Mar, 2016,Q1, 800.00, EUR

                    A,Mar, 2016,Q1, 900.00, GBP

                    B,Apr, 2016,Q2, 100.00, EUR

                    C,Apr, 2016,Q2, 100.00, GBP

                    A,May, 2016,Q2, 200.00, EUR

                    B,May, 2016,Q2, 300.00, DKK

                    C,May, 2016,Q2, 400.00, GBP

                    A,Jun, 2016,Q2, 500.00, EUR

                    B,Jun, 2016,Q2, 600.00, GBP

                    C,Jul, 2016,Q3, 700.00, GBP

                    A,Jul, 2016,Q3, 800.00, EUR

                    B,Aug, 2016,Q3, 900.00, EUR

                    C,Aug, 2016,Q3, 700.00, GBP

                    A,Sep, 2016,Q3, 1500.00, EUR

                    B,Sep, 2016,Q3, 1200.00, GBP

                    C,Oct, 2016,Q4, 1300.00, EUR

                    A,Oct, 2016,Q4, 1400.00, GBP

                    B,Nov, 2016,Q4, 1500.00, EUR

                    C,Nov, 2016,Q4, 1600.00, GBP

                    A,Dec, 2016,Q4, 1700.00, EUR

                    B,Dec, 2016,Q4, 1800.00, GBP

                    C,Jan, 2016,Q1, 1900.00, EUR

                    W,Dec, 2016,Q4, 1700.00, EUR

                    Y,Dec, 2016,Q4, 1800.00, GBP

                    Q,Jan, 2016,Q1, 1100.00, EUR];

                     

                    Dimension

                    Customer

                     

                    Expression

                    Sum({1<DateField = {"$(='>=' & vStart & '<=' & vEnd)"}>} SalesAmount)

                     

                    Capture.PNG