14 Replies Latest reply: May 12, 2017 1:37 PM by Dr.Maks Clay RSS

    Problem with Max(Month)!!!

    Dr.Maks Clay

      Hi, friends! )

      I need your consultation for the folowing problem.

      I have "Sales" Table, wich topology looks like this:

      ID_Product, Price, SaleDate, Qiantity

      I need to show in another table the products wich were sold in last month. The Last SaleDate is-June.

      I have tried to do this:sum({<Month(SaleDate)={'$(=max(month(SaleDate)))'}>} Quantity) but it does't works!

      Can anybody help me?!

        • Re: Problem with Max(Month)!!!
          Krishnapriya Arumugam

          Try below expression.

           

          sum({<Month(SaleDate)={'=$(=max(Month(SaleDate)))'}>} Quantity)

          or

          sum({<Month(SaleDate)={'=$(max(Month(SaleDate)))'}>} Quantity)

            • Re: Problem with Max(Month)!!!
              Dr.Maks Clay

              May be, I could't explaned what I wanted, but I'll try again.
              I want to get something like this: As I said, June is the last Month in all my DataBase, the last sale was in 13/06/2013.

               

              The result should be:

              Product_ID Last_Month
                 1                  24 (Product Quantity which I sold in June)

                 4                  2 (Product Quantity which I sold in June)

               

              How to do this?

            • Re: Problem with Max(Month)!!!
              Frank Hartmann

              Or maybe like that:

               

              sum({<SaleDate={'=$(=max(Month(SaleDate)))'}>} Quantity)

              • Re: Problem with Max(Month)!!!
                Vishwarath Nagaraju

                May be this: Not sure might have to tweak around the expression near date.

                 

                = sum({< SaleDate = { " =$(= '>=' & MonthStart(MonthStart(Max(SaleDate))-1) & '<'  & MonthStart(Max(SaleDate)) )"}>} Quantity)

                • Re: Problem with Max(Month)!!!
                  Sergey Shuklin

                  Hello, Dr.Maks Clay!

                   

                  Please look at the picture, is this what you want? (You need to create a variable with max date - it's usefull to use it over the project)

                  max_date.png

                    • Re: Problem with Max(Month)!!!
                      Dr.Maks Clay

                      Hi, Sergey! Thanks a lot for your answer.

                      I'll try to explane better, what I want to get!
                      For example: I have a Pencils, books, pens and notebooks wich I sale every day. Now, I want to know, how many Pencils, Books, Notebooks i have sold in Last Month!

                       

                      P.S: Last month is June, but there could be situation when i dont sale any books in june but sale pens.

                      Product            Jan     Feb     Apr      May         Jun           LastMonth

                      Pens                  20       2       0          22             1                    1

                      Books                 3        12      1         12             12                  12

                      Pencils                0          1      11        0              13                  13

                      Notebooks           1          3       12        11             11                 11

                        • Re: Problem with Max(Month)!!!
                          Vishwarath Nagaraju

                          If none of the above worked for you,

                          It would be easy if you can share a sample app with expected output.

                            • Re: Problem with Max(Month)!!!
                              Dr.Maks Clay

                              That's what I want to get!

                              Product            Jan     Feb     Apr      May         Jun           MaxMonth (In this situation is June)

                              Pens                  20       2       0          22             1                    1

                              Books                 3        12      1         12             12                  12

                              Pencils                0          1      11        0              13                  13

                              Notebooks           1          3       12        11             11                 11

                                • Re: Problem with Max(Month)!!!
                                  Digvijay Singh

                                  Hi,

                                  AS suggested by others you should have dual month field so that numeric comparison can be made.

                                  I just tried to handle your sample through pivot table, just had to add Monthno field but its not needed if your month field is extracted from the date field. Check this -

                                  Capture.PNG

                                  Script used -

                                   

                                  Table:

                                  CrossTable(Month,Value)

                                  Load * inline [

                                  Product,            Jan,    Feb,    Apr ,    May ,        Jun        

                                  Pens,                  20  ,    2  ,    0  ,      22  ,          1            

                                  Books  ,              3  ,      12 ,    1  ,      12  ,          12        

                                  Pencils ,              0 ,        1  ,    11 ,      0 ,            13        

                                  Notebooks  ,        1  ,      3  ,    12  ,      11  ,          11  ];    

                                   

                                   

                                  Left Join(Table)

                                  Load * inline [

                                  Month, MonthNo

                                  Jan,1

                                  Feb,2

                                  Apr,4

                                  May,5

                                  Jun,6 ];

                          • Re: Problem with Max(Month)!!!
                            Wallo Atkinson

                            I would probably create a variable for the max month and refer to that in m set analysis.

                            Something like:

                            Let vMaxSalesMonth=month(max(SalesDate));

                             

                            (But variable could be different based on what your data looks like.)

                             

                            Then your expression would be something like.

                            sum({<Month(SaleDate)={'$(vMaxSalesMonth)'}>}Quanitity)


                            Really, I would probably add a month field in your script so that it would be even simpler. 

                            sum({<MonthDate={'$(vMaxSalesMonth)'}>}Quanitity)

                              • Re: Problem with Max(Month)!!!
                                Dr.Maks Clay

                                Thanks a Lot, Wallo!
                                1) What about the situation, when I didn't sold anything in June? This Method will give me 0 or just "-" instead any number?;

                                2) When I want to get previous Month, I should do something like this?
                                let PreviousMonthSale=MONTH(MAX(SalesDate))-1;

                                In expression: sum({<Month(SaleDate)={'$(PreviousMonthSale)'}>}Quanitity)


                                Am I right?