topic SUM value for last date in that week in App Development

SUM value for last date in that week

SAM81 — Wed, 31 Mar 2021 14:40:09 GMT

I have a table that contains both week and date values. This table contains a measure called CREATED

I have a chart which trends the weekly CREATED volume, but i need this to only show the CREATED value for the last date within that week.

The data is as per the below example. Instead of showing the sum total value for wc15th, which would be 147, i would like it to only show the value for the last day within that week, so 44. For wc22nd, it should show 12

Any ideas?

Re: SUM value for last date in that week

shayd — Thu, 01 Apr 2021 09:52:09 GMT

Hi,

I'm not familiar with your data but if the table in the image shows the original script table
without any special manipulations maybe you can try to create a group by table on your
original table using script:

LOAD
WEEK_COMMENCING,

MAX(DATE)

CREATED
From {your table name}
Group By WEEK_COMMENCING, CREATED;

Good luck!

Re: SUM value for last date in that week

SAM81 — Tue, 06 Apr 2021 12:54:25 GMT

Thanks for the suggestion, but I couldn't quite get that to work as I needed. I have found the following solueiton on a different thread:

max(aggr(sum(aggr(sum(CREATED),WEEK_COMMENCING,DATE)),DATE))

This almost works perfectly, but returns the max value for that week, not the max value for the max date in that week.

Any ideas on how I can amend?

Re: SUM value for last date in that week

Quy_Nguyen — Wed, 07 Apr 2021 11:39:32 GMT

Hello,

Try this expression : FirstSortedValue(CREATED,-DATE)

Tested result: