cancel
Showing results for 
Search instead for 
Did you mean: 

Filtering 13 months of data by top 5 for current month

Former Member
0 Kudos

Hello,

I am wanting to create a table/chart to display the last 13 months worth of data filtered by the top 5 product categories for the current month.

I have to be able to have this dynamically gerenate, as I am unable to modify the filters each time the report is run.

I was doing this by having a little table to display the top 5 for the current month, and then filtering the 13 months of data based on that table, but this isn't working when I am unable to modify the report.

Is there any sort of formula that I can use to achieve this?

Thanks!

Stephanie

Accepted Solutions (0)

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi Stephanie,

For displaying the data only for the last 13 months, you can try using the DaysBetween() function. Now, 13 months would approximately be 390 days. So assuming that you have at least one object in your report that displays the date (say for e.g. "MyDate"), you can use the following formula: If DaysBetween(CurrentDate(),MyDate) < 390 Then 1 Else 0. Give a name to this formula, and then drag it onto the report filter pane. In the condition, set it as 'Equal to 1'. This will filter the data for the past 13 months, and then you can apply the ranking for the Top 5 as per your requirement.

Regards,

Alok.

Former Member
0 Kudos

I'm alright getting the 13 months data, it's the filtering that data by the top 5 for the current month that I am unsure on.

For example, I have 13 months worth of data in columns Customer, Order Number, Company, Description. I want to use the top 5 Customers for October to filter the 13 months of data. So ideally I would like to see only data from the 13 months from the top 5 customers (October's top 5 customers) without having to modify the report manually each month.

Former Member
0 Kudos

Hey StephanieW,

Use Ranking to get the Top5 customers for current running month.

Apply Rank on Months like if Months = 13 then do format for rest fore & background to white & white (Aleter will do this)

and Top 5 on basis of measure.

Use CurrentDate() function and get the month details like OCT/NOV.

Or

From your Month object also you can get this info.

Gracias...!!