1 Reply Latest reply: Feb 2, 2017 11:01 AM by Sunny Talwar RSS

    Sum of sales in last month

    Pichet Pichyangkul

      Hi,

       

      I'm trying to get sum of sales from last month, but I'm not sure how to do that. Could you please help me?

       

      Below is the example of my data.

      partner_idorder_dateorder_month_yearorder_monthorder_yearorder_daysales
      11/15/20172017_11201715500
      11/16/20172017_11201716100
      12/1/20172017_2220172200


      So since today is in February, I want the KPI Chart to shows 600 (sum sales of January)


      Thank you

        • Re: Sum of sales in last month
          Sunny Talwar

          1st I would create a MonthYear field in the script like this

           

          LOAD partner_id,

                     order_date,

                    Date(MonthStart(order_date), 'M-YYYY') as MonthYear,

                    order_month_year,

                    order_month,

                    order_year,

                    order_day,

                    sales

          FROM ....;

           

          and then use this expression:

          Sum({<MonthYear = {"$(=Date(AddMonths(Max(MonthYear), -1), 'M-YYYY'))"}>}sales)