4 Replies Latest reply: Jul 8, 2011 7:27 AM by mpsaei1205 RSS

    Trouble to show some months ago

      Hi, to all,

       

      Until yesterday this option was working fine, but when I changed my Cross Table to concatenate two field (T$PERI$O) this mean Month, then it not working anymore (see attached file that explain and show the setup option, I´ll appreciate your help to find the mistake.

       

      Tanks in advance,

        • Trouble to show some months ago
          Miguel Angel Baeyens de Arce

          Hi,

           

          First question is: are both tables loading months from same year? The one you are showing is using MakeDate() with year 2011. Has the concatenated table values from months from year 2011 as well?

           

          Miguel Angel Baeyens

          BI Consultant

          Comex Grupo Ibérica

            • Trouble to show some months ago

              Hi Miguel, Thanks for, you quick response

               

              Yes, Both Tables loading months from same year, the firts one is a Excell (Budget )and second one is my DB Oracle (Sales).

               

              Yes, I need to concateate it because the Month is a Synckey.

               

              I would like to compare Budget vs. real Sales from current year until tow months ago.

                • Re: Trouble to show some months ago
                  Miguel Angel Baeyens de Arce

                  Hi,

                   

                  Are you marking in the concatenation the source of the records (Budget and Sales)? If you don't, and if the amount field name is the same for both budget and sales, you cannot distiguish between both.

                   

                  So the concatenation should look like this

                   

                  Sales:
                  LOAD Date,
                       Amount,
                       'Sales' AS Source
                  FROM SalesSource;
                  
                  Budget:
                  LOAD Date,
                       Amount,
                       'Budget' AS Source
                  FROM BudgetSource;
                  

                   

                  Then your expression to get the budget can be

                   

                  Sum({< Source = {'Budget'} >} Amount)
                  

                   

                  And for sales

                   

                  Sum({< Source = {'Sales'} >} Amount)
                  

                   

                  Anyway, the complete script and some sample data would help, I'm afraid I cannot understand the document you attached.

                   

                  Hope that helps.

                   

                  Miguel Angel Baeyens

                  BI Consultant

                  Comex Grupo Ibérica

              • Re: Trouble to show some months ago

                Scrip

                SET ThousandSep=',';

                SET DecimalSep='.';

                SET MoneyThousandSep='.';

                SET MoneyDecimalSep=',';

                SET MoneyFormat='Bs #.##0,00;Bs -#.##0,00';

                SET TimeFormat='hh:mm:ss TT';

                SET DateFormat='DD/MM/YYYY';

                SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff] TT';

                SET MonthNames='Ene;Feb;Mar;Abr;May;Jun;Jul;Ago;Sep;Oct;Nov;Dic';

                SET DayNames='Lun;Mar;Mié;Jue;Vie;Sáb;Dom';

                OLEDB CONNECT TO [Provider=OraOLEDB.Oracle.1;Persist Security Info=False;User ID=baan;Data Source=baan_10.72.102.100;Extended Properties=""] (XPassword is EbOWBRJOPDdQWSJOTTYCWQZNVF);

                 

                SQL SELECT *               (HERE is MY LES INFORMATION)

                FROM BAAN.TTDSLS824804;  (Where T$PERI$O mean MONTH & T$DQUA$O Mean Sales Amount)

                SQL    SELECT "T$COPR",   

                "T$ITEM" "T$ITEM$O",

                "T$CWAR",

                "T$MATC",

                "T$OPRC"

                FROM BAAN.TTIITM001804;

                SQL SELECT "T$ITEM" "T$ITEM$O",

                "T$CWAR",

                "T$STOC",

                "T$ORDR",

                "T$ALLO"

                FROM BAAN.TTDINV001804;

                SQL SELECT "T$ITEM" "T$ITEM$O",

                "T$ORNO" "T$ORNO$O",

                "T$PONO" "T$PONO$O",

                "T$CUNO" "T$CUNO$O",

                "T$OQUA",

                "T$BQUA"

                FROM BAAN.TTDSLS041804;

                 

                LOAD * INLINE [

                T$PERI$O, Name

                   1, Ene

                   2, Feb

                   3, Mar

                   4, Abr

                   5, May

                   6, Jun

                   7, Jul

                   8, Ago

                   9, Sep

                  10. Oct

                  11, Nov

                  12, Dic

                ];

                 

                 

                Step1:

                crosstable(T$PERI$O, Cantidad, 4)  (T$PERI$O mean MONTH)

                LOAD

                T$YEAR$O,

                T$CITG$O,

                [Desc Familia],

                T$ITEM$O,

                [1] AS 1,

                [2] As 2,

                [3] AS 3,

                [4] AS 4,

                [5] As 5,

                [6] AS 6,

                [7] AS 7,

                [8] AS 8,

                [9] AS 9,

                [10] AS 10,

                [11] AS 11,

                [12] AS 12

                FROM

                [C:\Documents and Settings\AIZAGUIRRE\Escritorio\BI\Datos Prueba\PRESUPUESTO  2011.XLS]

                (biff, embedded labels, table is [data unidades y Dolares$]);

                Step2:

                NOCONCATENATE LOAD T$YEAR$O, T$CITG$O,[Desc Familia],T$ITEM$O,

                Month(MakeDate(2011,Num(Num#(T$PERI$O)))) AS T$PERI$O,

                Cantidad                          (Cantidad mean Budget AMOUNT)

                DROP TABLE Step1; 

                 

                 

                BUDGET EXCEL FILE Example

                T$YEAR$OT$CITG$ODesc FamiliaT$ITEM$O123456789101112
                2011PT7020Air Passenger(Air,Cabin,Panel)I-46340301511151215113
                2011PT7020Air Passenger(Air,Cabin,Panel)I-47040196496221396131996131316
                2011PT7020Air Passenger(Air,Cabin,Panel)I-421592235075205270
                2011PT7020Air Passenger(Air,Cabin,Panel)I-42524030143914038140912
                2011PT7020Air Passenger(Air,Cabin,Panel)I-46411114124224221
                2011PT7020Air Passenger(Air,Cabin,Panel)I-423303014811611610