11 Replies Latest reply: Jul 29, 2016 4:48 AM by Iskander Smagulov RSS

    Periods of dates in pivot table

    Iskander Smagulov

      Hi guys!

       

      I have a table with a number of items came in or out in each stock for each day.

      For example:

      DateStockIteminORout
      03.06.16Stock1Item13
      04.06.16Stock1Item1-2
      02.07.16Stock1Item22
      10.07.16Stock1Item2-1
      14.07.16Stock1Item1-1

       

      In Pivot table in the end should be:

      Year-MonthItemBalance
      06-2016Item11

       

      07-2016

      Item21
      Item10

       

      There also can be Quarter-Year or Week-Year instead of Year-Month.

      Please help to solve it. Thank you very much!

        • Re: Periods of dates in pivot table
          Prashant Sangle

          Hi,

           

          in script from dateField create Year Month,Quarter-Week and Week-Year Field

           

          and in front end

          take pivot table add

          required dimension and expression could be sum(inOrOut)

           

          Note : to create Year Month,Quarter-Week and Week-Year Field see date and time function in help menu.

           

          Regards,

            • Re: Periods of dates in pivot table
              Iskander Smagulov

              Hi! Thanks!

              I already created such fields in my Calendar. Everything ok with it.

               

              If I use sum(inORout) and take in pivot table Year-Month in dimension, so it will calculate only values for that period.

               

              Correct calculation will be such:

              06-2016 - sum of all values from the start date till the end of June

              07-2017 - sum of all values from the start date till the end of July

               

              Using your solution it will calculate only values for that periods, not from the start date.

               

              Thank you, Max, by the way!

                • Re: Periods of dates in pivot table
                  Prashant Sangle

                  Hi,

                   

                  In straight table > Expression > select Expression > Accumulation > select full Accumulation.

                   

                  or in Pivot chart see below thread

                  Accumulation for Pivot Table

                  Re: Accumulation in Pivot

                   

                  Regards

                  • Re: Periods of dates in pivot table
                    kvp kumar

                    Hi,

                    Some bit of confusion ,

                    start date means -

                    You need to calculate the values from the first row to current rows i.e year field contains the dates from 3-2016 to 7-2016

                    so for calculating the value for 7-2016 should be as an accumulation of 3-2016 to 7-2016

                    then in expression use the below expression.

                    rangesum(above(sum(inORout), 0 , RowNo()))

                    I hope my understand is correct or let me know .

                     

                     

                    Thanks

                    Kumar

                      • Re: Periods of dates in pivot table
                        Iskander Smagulov

                        Thank you, Kumar!

                        For me this solution is something new. And the result of it is very close for what I'm looking for.

                         

                        Here, what I made:

                        Operations:

                        LOAD * INLINE [

                            Дата, Stock, Item, inORout

                            03.06.2016, Stock1, Item1, 3

                            04.06.2016, Stock1, Item1, -2

                            02.07.2016, Stock1, Item2, 2

                            10.07.2016, Stock1, Item2, -1

                            14.07.2016, Stock1, Item1, -1

                        ];

                         

                        Also I have a calendar with Month-Year, Week-Year and Quarter-Year.

                         

                        In my frontend I made a pivot table with dimensions: Month-Year and Item

                        Expression (as you wrote): rangesum(above(sum(inORout), 0 , RowNo()))

                         

                        It shows in Month-Year - for June-0, July-1

                        But when I expand it shows June-Item1-1

                        July-Item1--1

                        July-Item2-0

                  • Re: Periods of dates in pivot table
                    Kaushik Solanki

                    Hi,

                     

                    What I do in such cases is calculate the Closing stock of every month in script using loop.

                     

                    Something like below.

                     

                    Data:

                    Load Date,Stock,Item,InORout from xyz;

                     

                    Month:

                    Load Distinct Monthstart(Date) as Month Resident Data;

                     

                    Let vCount = noofrows('Month');

                     

                    For i=0 to $(vCount)-1

                     

                         Let vMonth =   Peek('Month',$(i),'Month');  

                     

                         MOMStock:

                         Load Stock,Item,Sum(InORout) as Inventory,'$(vMonth)' as MonthStart

                         Resident Date <= '$(vMonth)';

                     

                    Next

                     

                    Drop table Data.Month;

                     

                    This way you will get the Stock for everymonth

                     

                    Regards,

                    Kaushik Solanki