Use the top 5 dates that are smaller than selected date as dimension

[msmichael]

Hi experts,

There are three fields in my data - Date, ProductId and Amount. On each date, there might be many ProductIds. The dates can go back to 1 year (meaning totally 365 distinct dates in Date field), something like below,

Date	ProductId	Amount
5/20/2020	101	10
5/20/2020	102	20
5/20/2020	103	100
4/30/2020	104	50
4/30/2020	101	12
4/30/2020	103	85
4/30/2020	102	41
4/15/2020	101	10
4/15/2020	102	35
4/1/2020	104	32
4/1/2020	102	70
3/31/2020	...	...
3/31/2020
3/20/2020
3/20/2020
3/15/2020
...

I have a list on Date field in my dashboard, user can choose one date to view products and amount. Now I need to add a bar chart which shows number of products for the most latest 5 days which are smaller than chosen date. 

For example, if user choose "4/30/2020", the x-axis on the bar chart should show dates 4/30/2020, 4/15/2020, 4/1/2020, 3/31/2020, 3/20/2020, y-axis should show number of productIds on the according date.

How can I do this? I am thinking about using calculated dimension on Dates but not sure how to do it.

Thanks,

Michael

[vamsee]

I am not sure about the dimension, you can try using Rank() in it. But, maybe try changing your expression to

Sum({<Date={"=rank(Max(Date),1,4)<6"}>}Amount)

[Brett_Bleess · Accepted solution]

See the following duplicate post for solution:

https://community.qlik.com/t5/QlikView-App-Development/Use-the-latest-5-dates-that-are-smaller-than-...

Regards,
Brett