11 Replies Latest reply: May 29, 2014 11:02 AM by Denis Lomakin RSS

    Sum by Max Date


      Hello,

      I have a table that contains warehouse, week and Inventory Units.

      I would like to build a table that will dispaly the inventory the warehouse and inventory for a last week.

      Something Like that:

      Raw data:

      Wharehouse      Date           Inventory

      A                          1                   500

      A                          2                   450

      B                          1                    300

      B                          2                     250

       

      Table:

      Wharehouse     Inventory

      A                             450

      B                            250

       

      Please Help

      Thank you

        • Re: Sum by Max Date
          Bruno Souza

          Hello, Denis.

           

          I think the easiest way is to create a new dimension, LastDate, in the script and check the option "Suppress When Value Is NULL" in the table. The code would be something like this:

           

          WharehouseInventory:
          Load * Inline [
              Wharehouse, Date, Inventory
              A, '01/01/2014', 500
              A, '02/02/2014', 450
              B, '01/01/2014', 300
              B, '02/02/2014', 250
          ];
          
          Left Join (WharehouseInventory) Load
            Wharehouse,
            Date(Max(Date)) as Date,
            Date(Max(Date)) as LastDate
          Resident WharehouseInventory
          Group by Wharehouse;
          
          

           

          An example app is attached.

           

          Cheers.

            • Re: Sum by Max Date

              Thank you Bruno,

              It works, but I would like to present the table without showing the date.

              Is it a way to do that?

              Thank you

                • Re: Sum by Max Date

                  HI Bruno,

                  Please ignore my previous post.

                  I just need to hide the column in Presentation tab.

                  Thank you for your help.

                  • Re: Re: Sum by Max Date
                    Bruno Souza

                    You're welcome, Denis.

                     

                    Even though you said to ignore your previous post, let me add this. I thought you'd want to display the date so I used the field LastDate. But you don't have to. You could create a flag, like this:

                     

                    WharehouseInventory:  
                    Load * Inline [  
                        Wharehouse, Date, Inventory  
                        A, '01/01/2014', 500  
                        A, '02/02/2014', 450  
                        B, '01/01/2014', 300  
                        B, '02/02/2014', 250  
                    ];  
                      
                    Left Join (WharehouseInventory) Load  
                      Wharehouse,  
                      Date(Max(Date)) as Date,  
                      1 as FlagLastInventory  
                    Resident WharehouseInventory  
                    Group by Wharehouse;
                    

                     

                    Then use the flag in your expression:

                     

                    Sum({$<FlagLastInventory = {1}>} Iventory)
                    

                     

                    Or you can create both fields and use each one where you think is best.

                     

                    Bruno.

                • Re: Sum by Max Date
                  Eduardo Sommer

                  Create a straight table with wharehouse as dimension and firstsortedvalue(Inventory, -Date) as the expression. Please note the minus sign in the Date field, since we want the last week.

                   

                  Eduardo

                    • Re: Sum by Max Date

                      Hi Edoardo,

                      Thank you for your responce.

                      When I use the formula I am getting no results.

                      Should I add anything else to expression?

                      Thank you

                        • Re: Re: Sum by Max Date
                          Eduardo Sommer

                          Hi,

                           

                          Look at the attached app. It's very easy

                           

                          Eduardo

                            • Re: Sum by Max Date

                              Hi Eduardo,

                              It works if you have only one dimensions - wharehouse, but if the table has more fields - as product type, product color etc the formula does not work properly.

                              I am not familiar with 'firstsortedvalue' function, but if it is possible to add the group by to it it would help.

                              Thanks  a lot

                                • Re: Sum by Max Date
                                  Eduardo Sommer

                                  Hi Denis

                                   

                                  If you have more dimensions, besides Wharehouse, they will work as a 'group by'. If you want to get the inventory of a color, of a product, of certain type, just add these dimensions and the expression will take the last inventory of that color of that product of that product type.

                                   

                                  The firstsortedvalue takes the first value in a group of records (be them in a loading script or in a chart- pivot or straight table, for example). In the case of the chart, the dimensions act as the group by in the load statement. When you want the last record, you put a minus sign in front of the order variable - kind of a lastsortedvalue. For this reason i put -Date in the function.

                                   

                                  It looks too simple to be true, but it works very fine.

                                   

                                  Eduardo

                          • Re: Sum by Max Date
                            jagan mohan rao appala

                            Hi Denis,

                             

                            Try like this

                             

                            Data:

                            LOAD

                            *,

                            If(RowNo() <> 1 AND Previous(WareHouse) <> WareHouse, 1, 0) AS MaxDateByWareshouse

                            FROM DataSource

                            ORDER BY WareHouse, Date;

                             

                            Now do this in chart

                            Dimension : WareHouse

                            Expression : Sum({<MaxDateByWareshouse={1}>}Inventory)

                             

                            Hope this helps you.

                             

                            Regards,

                            Jagan.