Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)