topic Re: Monthly sum of last available date in App Development

Monthly sum of last available date

Anonymous — Mon, 30 Apr 2018 08:56:14 GMT

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

Re: Monthly sum of last available date

Thiago_Justen_ — Tue, 01 May 2018 12:41:17 GMT

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')))

Re: Monthly sum of last available date

Anonymous — Wed, 02 May 2018 07:58:59 GMT

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

Re: Monthly sum of last available date

Thiago_Justen_ — Wed, 02 May 2018 16:51:46 GMT

Are you able to share an example app?

Re: Monthly sum of last available date

Anonymous — Thu, 03 May 2018 07:26:34 GMT

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