Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
rougeherring
Not applicable

Last 5 records from Each Month

I'm trying to get a sum in Qlik Sense that will be displayed in a bar chart

I need to show, by month, the last 5 records in the data based on each month (but not necessary from that month). I'm thinking it would need to include a combination of an aggr and recno but that's a guess.

For example:

 

DateMonthIDAmount
1-JanJanuary110
12-JanJanuary220
13-JanJanuary330
18-JanJanuary440
19-JanJanuary550
29-JanJanuary660
11-FebFebruary770
12-FebFebruary880
17-FebFebruary990
1-MarMarch10100
3-MarMarch11110
8-MarMarch12120
9-MarMarch13130

1-Apr         April

January would be the sum of ID 6,5,4,3,2

February would be the sum of ID 9,8,7,6,5

March would be the sum of ID 13,12,10,9

April would also be the sum of ID 13,12,11,10,9

1 Solution

Accepted Solutions
ahaahaaha
Not applicable

Re: Last 5 records from Each Month

Hi Brandon,

It has become interesting to try to solve your task. I propose to solve the task using the data island, transferring all the calculations to the script. In the attached file QVF, as an variant, an implementation example. All comments are in script code.

I will be glad if I could help you.

Regards,

Andrey

2 Replies
ahaahaaha
Not applicable

Re: Last 5 records from Each Month

Hi Brandon,

It has become interesting to try to solve your task. I propose to solve the task using the data island, transferring all the calculations to the script. In the attached file QVF, as an variant, an implementation example. All comments are in script code.

I will be glad if I could help you.

Regards,

Andrey

jens_leysen
Not applicable

Re: Last 5 records from Each Month

Hey Brandon,

In this case your ID keeps going up together with the date. When you select a date/month, you can use MAX(ID) to find out what your highest ID is or better said, your starting point. At this point you can add the 4 ID's previous to this point and add them to your MAX(ID) and you will have the 5 highest ID's.

Hope this works.