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_id | order_date | order_month_year | order_month | order_year | order_day | sales |
---|---|---|---|---|---|---|
1 | 1/15/2017 | 2017_1 | 1 | 2017 | 15 | 500 |
1 | 1/16/2017 | 2017_1 | 1 | 2017 | 16 | 100 |
1 | 2/1/2017 | 2017_2 | 2 | 2017 | 2 | 200 |
So since today is in February, I want the KPI Chart to shows 600 (sum sales of January)
Thank you
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)