Help needed with lastValue

Help needed with the use of lastValue (and firstValue).

I have a simple dataset that has the number of customers by segment and date, and I want to extract the current number of clients by segment and date. I thought:

lastValue(customers, [date ASC], [segment])

would do it, but it does not.

Data looks like:
date | segment | customers
2022-08-14 | A | 1000
2022-08-14 | B | 100
2022-08-14 | C | 10
2022-08-15 | A | 1050
2022-08-15 | B | 110
2022-08-15 | C | 11
2022-08-16 | A | 1078
2022-08-16 | B | 120
2022-08-16 | C | 9

Here’s the table I need to plot in Quicksight to visualize the result of the lastValue:

I need to add “customers” to the rows for it not throwing an error.

Any help is appreciated!

You can add “customers” to row and then hide the column like below.

Here is final result look like:

I hope this helps!

Tejal Gohil

Thanks for your response Tejal!

The result I want to achieve is something more like a lastOver ordered by Date partitioned by Segment (actually what lastValue is supposed to do) where I can get the last value for each segment and show it in a KPI PRE_FILTER

1 Like

Have you looked into doing a top / bottom filter based on your condition.

To get the most recent date I would create a calculated field that takes the difference from now until your date.

time_diff = dateDiff({date},now(),‘DD’)

Then filter your kpi to be the top 1 date based on this newly time_diff calculated field.

Let me know if that helps!

1 Like