10 Replies Latest reply: Oct 30, 2017 5:51 PM by Fabrizio Giorgio RSS

    Divide dimension by Variable in a script

    Fabrizio Giorgio

      Hi all,

       

      This question is a continuation of a previous thread: Re: Variable in Script - Assign Value based on If Statement

       

      I have a variable v12WeekAverageCalc that has been defined as follows: (Thanks to Vish's help  )

       

      = Pick(vReportCurrent_Period, 24, 24, 24, 15, 15, 15, 18, 18, 18, 21, 21, 21)

       

      This picks up the current financial period (defined in vReportCurrent_Period) from my database and assigns a predefined numerical value based on the current financial period.

       

      Period 1,2,3 = 24

      Period 4,5,6 = 15

      Period 7,8,9 = 18

      Period 10,11,12 = 21

       

      I need to use these numerical values to calculate average QTY sales based on a number of months...

       

      variable settings.JPG

      vReportCurrent_Period is defined as follows in another part of my script:

       

      Period:

      LOAD "fin_year" as FinancialYear,

      "Period" as FinancialPeriod;

      SQL SELECT "fin_year", "Period"

      FROM PUB.perlive

      where Module ='iv';

       

       

      LET vReportCurrent_FinYear = FieldValue('FinancialYear',1);

      LET vReportCurrent_Period =  FieldValue('FinancialPeriod',1);

      DROP TABLE Period;

       

      Both v12WeekAverageCalc and vReportCurrent_Period are giving the correct output.



      Now I need to use v12WeekAverageCalc in a calculation as follows.


      QTYSalesValueX / v12WeekAverageCalc = 12WeekAvgQTYSales

       

      here's my actual script for that section:

       

      ISHRB12WeekAverageQtySales:

      Load

      ISHRBSKU,

      (ISHRB12WeekAvgQTYSales_temp / $(v12WeekAverageCalc)) as ISHRB12WeekAvgQTYSales

      Resident ISHRB12WeekAverageQtySales_temp;

       

      I need to display ISHRB12WeekAvgQTYSales in my pivot chart.


      I am getting this error:


      variable script error.jpg

      Can anyone help??

       

      Thanks in advance...

       

      Cheers,

       

      Fab

        • Re: Divide dimension by Variable in a script
          Vishwarath Nagaraju

          Can you upload the sample here i will see what i can do. I do not need to run the data from database. Or if you can load all your fields you are pulling from your database and use table box to add all fields and send that to excel. Then upload your excel file if possible.

            • Re: Divide dimension by Variable in a script
              Fabrizio Giorgio

              OK Vish,

               

              Give me some time and I will do that shortly...

               

              thanks again for your help...

               

              Fab

                  • Re: Divide dimension by Variable in a script
                    Fabrizio Giorgio

                    Hi Vish,

                     

                    I have attached a sample QVW and the data exported to XLS from a table object within my qvw. Thanks for suggesting that...

                     

                    Sorry for the delay, this is the first time i've created a sample qvw...

                     

                    Just for your info, the final calculation i'm trying to achieve is:

                     

                    (((ISHRBPreviousYTDSaleTotals + ISHRBCurrentYTDSaleTotals) * 3) / $(v12WeekAverageCalc))

                     

                    you can scroll down to the bottom of the script where i was trying to get this to work..

                     

                    I have replaced $(v12WeekAverageCalc) with 15 for testing purposes

                     

                    here is the excerpt fom that part of the script...

                     

                    ////////////////////////////////////////////////////////Calculate 12 Week Average//////////////////////////////////////////////////

                     

                     

                    LEFT KEEP (ISHRBYTDQTYSales)

                     

                     

                    ISHRB12WeekAverageQtySales_temp:

                    Load

                    ISHRBSKU,

                    ((ISHRBPreviousYTDSaleTotals + ISHRBCurrentYTDSaleTotals) * 3) as ISHRB12WeekAvgQTYSales_temp

                    Resident ISHRBYTDQTYSales;

                     

                     

                    ISHRB12WeekAverageQtySales:

                    Load

                    ISHRBSKU,

                    (ISHRB12WeekAvgQTYSales_temp / 15) as ISHRB12WeekAvgQTYSales

                    //(ISHRB12WeekAvgQTYSales_temp / $(v12WeekAverageCalc)) as ISHRB12WeekAvgQTYSales

                    Resident ISHRB12WeekAverageQtySales_temp;

                    //

                    //DROP TABLE ISHRB12WeekAverageQtySales_temp;

                    //

                    //Left Join (ISHRBYTDQTYSales)

                    //

                    //Load

                    //ISHRBSKU,

                    //(Round)ISHRB12WeekAvgQTYSales

                    //Resident ISHRB12WeekAverageQtySales;

                    //

                    //DROP TABLE ISHRB12WeekAverageQtySales;

                      • Re: Divide dimension by Variable in a script
                        Vishwarath Nagaraju

                        Need sometime to look into this. Ok.?

                        • Re: Divide dimension by Variable in a script
                          Vishwarath Nagaraju

                          Ok try these two scripts.

                          After below left keep.

                           

                          We have to do some changes to the variables. Like Ctrl+Alt+V

                          In your variable overview just change exactly to what you can see in the screen shot below.

                           

                          Pick(Match($(i), 1,2,3,4,5,6,7,8,9,10,11,12), 24, 24, 24, 15, 15, 15, 18, 18, 18, 21, 21, 21)

                          OR

                          Pick($(i), 24, 24, 24, 15, 15, 15, 18, 18, 18, 21, 21, 21)

                          Capture.PNG

                           

                          LEFT KEEP (ISHRBYTDQTYSales)


                          FOR i = 1 to $(vReportCurrent_Period)

                           

                          ISHRB12WeekAverageQtySales_temp:

                          Load

                          ISHRBSKU,

                          ((ISHRBPreviousYTDSaleTotals + ISHRBCurrentYTDSaleTotals) * 3)/$(v12WeekAverageCalc) as ISHRB12WeekAvgQTYSales_temp

                          FROM [HRB Sample data.xls]

                          (biff, embedded labels, table is Sheet1$);;

                           

                          Next;

                           

                          FOR i = 1 to $(vReportCurrent_Period)

                           

                          ISHRB12WeekAverageQtySales:

                          Load

                          ISHRBSKU,

                          (ISHRB12WeekAvgQTYSales_temp /$(v12WeekAverageCalc) ) as ISHRB12WeekAvgQTYSales

                          //(ISHRB12WeekAvgQTYSales_temp / $(v12WeekAverageCalc)) as ISHRB12WeekAvgQTYSales

                          Resident ISHRB12WeekAverageQtySales_temp;

                          Next;

                           

                           

                          Let me know how it goes.

                            • Re: Divide dimension by Variable in a script
                              Fabrizio Giorgio

                              Hi Vish,

                               

                              Thank you so much for your help...

                               

                              I have taken your suggestions and tried a few things...

                               

                              but this seems to have worked:

                               

                               

                              I've changed the v12WeekAverageCalc variable value to:

                               

                              Pick(Match($(vReportCurrent_Period), 1,2,3,4,5,6,7,8,9,10,11,12), 24, 24, 24, 15, 15, 15, 18, 18, 18, 21, 21, 21)

                               

                               

                              And amended the script as follows:

                               

                               

                              LEFT KEEP (ISHRBYTDQTYSales)

                               

                              ISHRB12WeekAverageQtySales_temp:

                              Load

                              ISHRBSKU,

                              ((ISHRBPreviousYTDSaleTotals + ISHRBCurrentYTDSaleTotals) * 3) as ISHRB12WeekAvgQTYSales_temp

                              Resident ISHRBYTDQTYSales;

                               

                              ISHRB12WeekAverageQtySales:

                              Load

                              ISHRBSKU,

                              (ISHRB12WeekAvgQTYSales_temp /$(v12WeekAverageCalc) ) as ISHRB12WeekAvgQTYSales

                              Resident ISHRB12WeekAverageQtySales_temp;

                               

                               

                              DROP TABLE ISHRB12WeekAverageQtySales_temp;

                               

                              It looks like it's working correctly... (I even tested changing the results to make sure it is working)

                              We will be going into a new financial period on wednesday, so we'll be able to run more checks...

                               

                              Thanks again for your invaluable input...

                               

                              Fab