Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Use the latest 5 dates that are smaller than chosen date as dimension

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,

DateProductIdAmount
5/20/202010110
5/20/202010220
5/20/2020103100
4/30/202010450
4/30/202010112
4/30/202010385
4/30/202010241
4/15/202010110
4/15/202010235
4/1/202010432
4/1/202010270
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

Labels (1)
1 Solution

Accepted Solutions
Highlighted
Creator
Creator

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

View solution in original post

4 Replies
Highlighted
Master
Master

@msmichael 

May be this :

dimension ->Date

Measure->

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

 

and then :

Capture.PNG

result:

Capture.JPG

attached qvw file

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Highlighted
Master
Master

Capture.JPG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Highlighted
Creator
Creator

Hi @Taoufiq_Zarra ,

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

Capture.PNG

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.

Capture.PNG

I'm using QvlikView April 2019 SR2 Version 12.40.20200.0 

 

 

Thanks,

Michael

Highlighted
Creator
Creator

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

View solution in original post