Base: query that shows percentage change based on date

[MikeSchelding]

Hi,

I have a table that looks like the following:

Name | Month | Income | Sales

Mike | 07/2017 | 10000 | 500

Anna | 07/2017 | 12000 | 125

John | 07/2017 | 09000 | 90

Mike | 08/2017 | 11000 | 600

Anna | 08/2017 | 15000 | 130

John | 08/2017 | 07000 | 80

I have tousands of rows.

I want to make a query that:

1. Filter the rows based on the sales number (> 100)
2. Shows the monthly change in sales and in income

Name | Month | Sales | Sales Change | Income Change

Mike | 08/2017 | 10000 | 20% | 10%

Anna | 08/2017 | 15000 | 04% | 25%

Is this possible?

[Ratslinger]

Hello,

Not knowing which DB or version you are using or some of criteria to be used, the following is based upon HSQLDB v1.8 embedded (Base default). The month viewed is input via parameter.

Of course, table and field names may need changing according to your table.

If this answers your question please click on the (upper left area of answer).

[MikeSchelding]

Thanks, I’ve marked your answer as correct, because you have achieved what I’m trying to do. Anyway, I couldn’t run your query. Should I run it using “Queries” → “Create Query in SQL View” ?

Please check my error:

[Album] imgur.com

[Ratslinger]

You have a couple of things wrong. In two items ( SELECT * FROM "PEOPLESALES" WHERE you have as ( SELECT * FROM "PEOPLESALES") WHERE which wrongly contains an extra right parenthesis. Because of this, it gave you a syntax error. You the turned on Run SQL command directly which won’t work here because it bypasses the ability of the parameter. Delete the two incorrect right parenthesis & turn off Run SQL command directly. ‘Create Query in SQL View’ is my almost always approach.