How to get the last value of the month

Q&A
,
1

Hi all,

I have the following sample data

|Date| Count | Column 3 | Column 4|

|— | — | — | —|
|2023-03-29 | 123 | | |
|2023-03-30 | 43 | | |
|2023-03-31 | 123 | | |
|2023-04-29 | 54 | | |
|2033-04-30 | 235 | | |
| | | | |

I have a scenario where I need to get the data of the last day of the month.
Eg. 31st Mar,30th Apr,31st May and so on.

The value should not be aggregated because I only want to see the value of the last day.
Result will be like for March it will be 123 , for April it will be 235

Anyone can give some ideas?

Thanks.

2

Hi @ku1918

Create a calculated filed with rank and order date descending, grouped by Month/Year.

rank([{Date-field} DESC], [extract(‘YYYY’, {Date-field}), extract(‘MM’, {Date-field})])

Use Rank calculated field as filter and set filter condition equal to 1
image

Here is the results: Listing last day of the month and value

image

1 Like
3

Is this able to cater for month that havent end yet?
Means like if today is 13th , it will get the 13th day value.

4 
rank([{capturetime} DESC],[extract(‘YYYY’, {capturetime}), extract(‘MM’, {capturetime})])

This give me syntax error. my capture time already in date format
image

5

Yes, It gives rank 1 to max date of the month. It doesn’t need to be full month.

What is the error message ?

1 Like
6

image
image705×128 7.84 KB

Here is the error.Not sure how to debug this.

7

I have tested the calc you shared and I see single quote is causing the error.

Please update the highlighted part. Instead of copy pasting, type it. Should fix the error.
image

Correct format
image

2 Likes
8

Thanks Ashok.

Look like it working. May I understand how does rank work for my understanding?

Does it mean it rank the date?

9

Yes, This function groups the date field based on grouping defined “[extract(‘YYYY’, {Date-field}), extract(‘MM’, {Date-field})]” and then applies rank to the field per the ordering. in this case [{Date-field} DESC] descending. Rank can also be applied to non-date dimension fields as well.

More information available here : Rank - Amazon QuickSight

1 Like