6 Replies Latest reply: Jul 26, 2017 5:00 PM by Andrew Walker RSS

    Load previous week data as additional column

    Gordana Lozo

      Hi all!

       

      I have a problem that I was trying to solve for a few days, but still cannot get it correct when I load data.

       

      I have current table like this:

       

      Opportunity No

      Opp. Value

      Script Execution Date

      Script Execution Week

      123

      1000

      2017-7-1

      2017-26

      123

      1200

      2017-7-8

      2017-27

      123

      1300

      2017-7-15

      2017-28

       

      I would like to have a table loaded like this:

       

      Opportunity No

      Opp. Value

      Previous Week Opp Value

      Script Execution Date

      Script Execution Week

      Prev Script Execution Week

      123

      1000

      null

      2017-7-1

      2017-26

      2017-25

      123

      1200

      1000

      2017-7-8

      2017-27

      2017-26

      123

      1300

      1200

      2017-7-15

      2017-28

      2017-27

       

      I have all figured out, except how to load “Previous Week Opp Value”.

       

      For some reason I am stuck with it :/, and it really should not be that difficult… I would appreciate any suggestion.


      Thank you!!

        • Re: Load previous week data as additional column
          Gordana Lozo

          Just to add the Load script:

           

          Load Opportunity_No,

                    Opp_Value,

                    Script_Execution_Date,

                    Script_Execution_Week,

                    If(week(Script_Execution_Date)=1,  Year(Script_Execution_Date)-1 & '-' & week(Script_Execution_Date - 7), Year(Script_Execution_Date) & '-' & week(Script_Execution_Date - 7)) as Previous_Script_Execution_Week;

          • Re: Load previous week data as additional column
            Mouna Chandra Konidhina

            Please look at the attached qvw.

             

            Capture1.PNG

            Capture.PNG

            • Re: Load previous week data as additional column
              Andrew Walker

              Hi Gordana

              This script will work as long as the data is loaded in date order:

              Data:

              LOAD [Opportunity No],

                   [Opp. Value],

                   Previous([Opp. Value]) as [Previous Week Opp Value],

                   Date([Script Execution Date],'YYYY-M-D') as [Script Execution Date],

                   Year(Date([Script Execution Date],'YYYY-M-D')) & '-' & Week(Date([Script Execution Date],'YYYY-M-D')) as [Script Execution Week],

                   Year(Date([Script Execution Date],'YYYY-M-D')-7) & '-' & Week(Date([Script Execution Date],'YYYY-M-D')-7) as [Prev Script Execution Week]

              FROM

              Book1.xlsx

              (ooxml, embedded labels, table is Sheet1);

               

              OR

               

              You can keep the script really simple:

               

              Data:

              LOAD [Opportunity No],

                   [Opp. Value],

                   Date([Script Execution Date],'YYYY-M-D') as [Script Execution Date]

              FROM

              Book1.xlsx

              (ooxml, embedded labels, table is Sheet1);

               

              and use chart expressions:

               

              Opportunity No Opp. Value

              Previous Week

              Opp Value

              Script Execution

              Date

              Script Execution

              Week

              Prev Script Execution

              Week

              1231000 2017-7-12017-262017-25
              123120010002017-7-82017-272017-26
              123130012002017-7-152017-282017-27

               

               

              Dimensions are [Opportunity No] and [Script Execution Date]

               

              Expressions:

              Opp. Value = sum([Opp. Value])

               

              Previous Week Opp Value = Above(TOTAL sum([Opp. Value]))

               

              Script Execution Week =

              Year(Date([Script Execution Date],'YYYY-M-D')) & '-' & Week(Date([Script Execution Date],'YYYY-M-D'))

               

              Prev Script Execution Week=

              Year(Date([Script Execution Date],'YYYY-M-D')-7) & '-' & Week(Date([Script Execution Date],'YYYY-M-D')-7)

               

              Regards

               

              Andrew