Show Count from the latest date

[qlikwiz123]

I have sales numbers coming in weekly once. The sales column has Number of Sales per category and the date column has the date of the week they were processed. 

How do I show the Sum(Sales) from the latest week?

For example, on 10th of February, I have data for last week which has received date as 2/1/2023. I need to show total sales which were received on previous week (in this case from received date 2/1/2023)

[sidhiq91]

@qlikwiz123  Could you please post some sample data? As per your explanation above Max(Date) should resolve the issue. Just to have more clarity on the requirement, it would be better if you can post some data.

[qlikwiz123]

Category	Date	Sales
A	1/1/2023	45
B	1/1/2023	89
C	1/1/2023	33
A	1/8/2023	12
B	1/8/2023	16
C	1/8/2023	76
B	1/15/2023	34
D	1/15/2023	999
A	1/22/2023	56
C	1/22/2023	54
B	2/1/2023	87

So in this case, since 2/1/2023 is the last date, I need to show Sum(Sales) from that last date in the data. It has to be dynamic as the dates change.

[sidhiq91 · Accepted solution]

@qlikwiz123  Please use the below expresssion.

Sum({<Date={"=Date($(VMax7Date)-7,'MM/DD/YYYY')"}>}Sales)

where VMax7Date=Max(Date)