Report to compare to previous period / date range

V5 Question
1

I have a client who has requested the item sales report to show sales for the current week then comparison to the previous week. (or essentially current date range compared to previous same length date range).

Is this possible? Since the reports work off the selected work period or date range, I’m not sure how to go about specifying the previous date range.

2

Its possible in fact I have done it in the past when we discussed Metrics and Dashboards. You can even use symbols like arrows etc or generate graphs. Let me see if I can show you an example.

1 Like
3

Heck in fact we used to have a Navigate widget that displayed this info.

4

This is something I have wanted to revisit give me a while to research it but I know I was able to do it in the past. It did involve some scripting and sql.

5

Thanks that would be great :slight_smile:

6

I found it. Here is the tile I used.

image

Here is the KPI script.

function ticketCount()
{
var thisMonthTickets = sql.Query('@@TicketCount').ThisMonth.First;
var previousMonthTickets = sql.Query('@@TicketCount').PreviousMonth.First;
var thisAvg = Math.round(thisMonthTickets / dt.ThisMonth.Days);
var prevAvg = Math.round(previousMonthTickets / dt.PreviousMonth.Days);

var inc = thisAvg > prevAvg;
var sym = inc ? tag.Color('green').Sym('▲') : tag.Color('red').Sym('▼');
var header = tag.Add(sym).Add(thisMonthTickets).Add('Tickets').Get();
var footer = tag.Add(dt.PreviousMonth.MonthName).Add(previousMonthTickets).Get('tickets');

var todayTickets = sql.Query('@@TicketCount').Today.First;
var footer2a = (prevAvg - todayTickets) + ' more tickets needed';
var footer2b = (todayTickets - prevAvg) + ' tickets ahead. Keep going';
var footer2 = inc ? footer2b : footer2a;
footer = tag.Add(footer).Linebreak().Add(footer2).Get();
return tag.Size(30).Add(header).Linebreak().Linebreak().Size(14,footer);
}

Here is the SQL.

Handler @@TicketCount

Select count(*) from Tickets where Date > '{Start}' and Date < '{End}'
1 Like
7

Thats not exactly what you asked but it should give you an idea. He may like a tile view better.

You can edit the SQL and script to reflect sales if you wish. That was demonstrating it with Ticket Counts

8

If he specifically wants it in a report then You may need to build an Entity Screen to view that report.

1 Like