4 Replies Latest reply: Dec 8, 2017 5:37 AM by Matheus Colares RSS

    Two Dimensions in One

    Matheus Colares

      Guy,

           I need create a Pivot Table with two expression, simple, but not.

           I have two dates, the first is the date which is due date and the second is data os payment, and a values what is the value of the note...OK

           I have to create de following table:

        

      PAIDNO PAID YET
      PeríodoR$ Mercadoria R$ Mercadoria
      jan/17sum(value)sum(value)
      fev/17sum(value)sum(value)
      mar/17sum(value)sum(value)
      abr/17sum(value)sum(value)
      mai/17sum(value)sum(value)
      jun/17sum(value)sum(value)
      jul/17sum(value)sum(value)
      ago/17sum(value)sum(value)
      set/17sum(value)sum(value)
      out/17sum(value)sum(value)
      nov/17sum(value)sum(value)
      dez/17sum(value)

      sum(value)

       

      Where this "Período" is just a jan2017, not is nothing with link..

      i mean, in expression PAID i have sum all values which date "Due Date" and in expression NOT PAID YET i sum all values which date "Date of Payment" , month by month. I say this because one note can have :

       

       

      Due Date : 01/01/2017

      Date os Paymente: 01/02/2017

      Value: $ 60

       

       

      Anyone knows how a can make this?

        • Re: Two Dimensions in One
          Anil Babu

          Not sure where value $60 coming from? Can you make one simple table and w.r.t output

          • Re: Two Dimensions in One
            Luis Madriz

            Hi,

             

            I think this should help you:

            Untitled.png

             

            Assuming data like this:

            Untitled.png

             

            And generating the Months like this:

            Data:

            LOAD *,

                 Dual(Month(DueDate)&'-'&Year(DueDate),MonthStart(DueDate)) as MonthDueDate,

                 Dual(Month(PayDate)&'-'&Year(PayDate),MonthStart(PayDate)) as MonthPayDate;

            LOAD

                Note,

                DueDate,

                PayDate,

                Value

            FROM [lib://AttachedFiles/DuePayDate.xlsx]

            (ooxml, embedded labels, table is Sheet1);

             

            I hope this helps,

             

            Cheers,

             

            Luis

            • Re: Two Dimensions in One
              Matheus Colares

              Hi, thanks  for the feedback.

               

                   Anil Babu, this $60 is just a sample, a data in my data base.

                   Luis Madriz, it's almost so, but in your exemplo, i have to do like this.

               

              paidnot paid
              jan/17450200
              fev/1745002150
              mar/17-1600
              apr/17-1000

               

                   You understand? This dimension Month don't can be any data load, is just an indicator...

               

              Thanks for all answer!!!

              • Re: Two Dimensions in One
                Matheus Colares

                I did it.

                I create a inline like this:

                DATA_GERAL:

                LOAD * INLINE [

                DATAGERAL

                01/01/2017,

                01/02/2017,

                01/03/2017,

                01/04/2017,

                01/05/2017,

                01/06/2017,

                01/07/2017,

                01/08/2017,

                01/09/2017,

                01/10/2017,

                01/11/2017,

                01/12/2017,

                ];

                 

                 

                DATA_BASE:

                LOAD

                MONTHNAME(DATAGERAL) AS MESANO

                RESIDENT DATA_GERAL;

                DROP TABLE DATA_GERAL;

                 

                And na expression i did:

                SUM({1} IF(MONTHNAME(CPI_DATA_BAIXA) = MESANO, CPI_VALOR)