Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I got data that look like this:
DATE | COUNTRY | CUSTOMER BASE DAILY |
---|---|---|
2018-01-30 | US | 0 |
2018-01-30 | AU | 1 |
2018-01-31 | US | 1 |
2018-01-31 | AU | 1 |
2018-02-27 | US | 5 |
2018-02-27 | AU | 1 |
2018-02-28 | US | 5 |
2018-02-28 | AU | 1 |
2018-03-29 | US | 6 |
2018-03-29 | AU | 1 |
2018-03-30 | US | 7 |
2018-03-30 | AU | 2 |
2018-04-13 | US | 7 |
2018-04-13 | AU | 3 |
2018-04-15 | US | 8 |
2018-04-15 | AU | 3 |
and I'd like to create a table where the dimension is the month and measure is the sum of the customer base column of the most recent date of that month: for the past month it, usually, coincides with the last day of the month but for the current month it must show the most recent available values.
So the table above should generate a table like this one:
MONTH | CUSTOMBER BASE |
---|---|
2018-01 | 2 |
2018-02 | 6 |
2018-03 | 9 |
2018-04 | 11 |
Could you help me with this?
Thanks,
Yari
Do you have a field called Month (mm format)?
Considering a negative answer I'd suggest you:
As dimension:
Date (DATE,'YYYY-MM')
As expression:
Sum(Aggr(If(DATE=Max(Total DATE),[CUSTOMER BASE]),Date (DATE,'YYYY-MM')))
Thanks Thiago for your answer, but unfortunately it doesn't work.
To give you some more info: I do have a [DATE.autoCalendar.YearMonth] field, which it's the one I'm using as dimension. I tried using the formula you provided "as is" and also changing Date (DATE,'YYYY-MM') to use [DATE.autoCalendar.YearMonth] but with no results: in both cases I get all values equal to 0.
Yari
Are you able to share an example app?
Please find the test app attached: I included both widgets using the solution you proposed and another one using the autocalendar YearMonth field.
Best Regards,
Yari