Use the latest 5 dates that are smaller than chosen 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

[Taoufiq_Zarra]

@msmichael 

May be this :

dimension ->Date

Measure->

=count({<Date={"<=$(=GetFieldSelections(Date))"}>} ProductId)

and then :

result:

attached qvw file

[Taoufiq_Zarra]

[msmichael]

Hi @Taoufiq_Zarra ,

Thanks for the answer. Somehow I see different Presentation tab from yours. This is what I see in my Presentation tab:

I do see a Dimension Limits tab though, however, I tried selecting the first option (see screenshot below), but it gives me the dates on which we had maximum counts, but not the most recent 5 days.

I'm using QvlikView April 2019 SR2 Version 12.40.20200.0 

Thanks,

Michael

[msmichael · Accepted solution]

Hi,

never mind. I added a new expression to get the Dates which are smaller than selected Date, and make the express the first one in Expressions tab and make it invisible, then in Dimension Limits tab I only show largest 5 values by the first expression and it works just fine.

Thanks,

Michael