Sorting with "Measures Samples Group By"

[Lyonel]

Hi,

I am trying to display on a graph the number of requests logged per service and keep only the top 5 over the last 3 month (to show the evolution).

But I cannot get to sort the data... Is it just me being a noob? Or is it not possible at this stage?

Here is what I get:

Here is what I would like (x3 as I need 3 month):

Any help would be appreciated!!! Thanks

[Lyonel]

Any ideas? 

[Lyonel]

Can anybody help me? @Victor maybe?

[Victor]

@Lyonel mmm... I don't think is possible  ... maybe you could get the info you need from a report... I'll have a look if you like and if report would be a suitable alternative...

[Lyonel]

@Victor a report would work indeed, it's just that I would like these stats on a dashboard... Any change a developer could look at it? Maybe a simple switch or "Order By" drop down would be enough?

[Guest]

Hi @Lyonel

Just had a thought about this one. Perhaps a workaround here is to not use a measure, but to use the "SQL Group By" option when creating a chart. 

So my idea is to have 3 separate widgets - one each for 1, 2, and 3 months ago. And you use some SQL operations in the criteria to specify the date range instead of using the in built date range functionality. 

In my example below, this show "Last Months" Incidents, grouped by Service

The key thing here is that the Period Filtering is set to "None" but my criteria is set to:

h_datelogged >= LAST_DAY(NOW() - INTERVAL 2 MONTH) + INTERVAL 1 DAY and h_datelogged < LAST_DAY(NOW() - INTERVAL 1 MONTH)

(I'm sure there are cleaner ways to do this but this seemed to work for me )

Then I would create two more of these widgets, and simply change the date range criteria to:

 

2 Months Ago:

h_datelogged >= LAST_DAY(NOW() - INTERVAL 3 MONTH) + INTERVAL 1 DAY and h_datelogged < LAST_DAY(NOW() - INTERVAL 2 MONTH)

 

3 Months Ago:

h_datelogged >= LAST_DAY(NOW() - INTERVAL 4 MONTH) + INTERVAL 1 DAY and h_datelogged < LAST_DAY(NOW() - INTERVAL 3 MONTH)

 

Then finally, stick them all onto a Dashboard, side by side, 

Would this work?

 

Kind Regards

Bob

 

 

[Lyonel]

@Bob Dickinson that is not a bad idea! I will give it a try and let you know. But surely I am not the only one with this issue, am I?

[Lyonel]

Not as nice as I would have liked, but it certainly does the job

Thanks @Bob Dickinson for the idea!