Orders v Invoicing BAQ

[Adrian_Mepham]

Hi all,

I am attempting to create a BAQ, for later use in a dashboard. What I am attempting to achieve is to have columns for Date, Orders, Average of Orders, Invoiced, Average Invoices. Then I want it to list every working day (Mon to Fri) with the results on each row, even if there were no orders or no invoices on that date. Obviously I will filter the results but how can I get each date to appear in the first column? At the moment I am using OrderDate but that is no good if no orders were taken on that date.
Thanks in advance
Adrian.

[ckrusen (Calvin Krusen)]

The quick and dirty way would be to make a UD table that just lists all the dates. Make it table #1 with the join to OrderHed as left outer (Show all records from UDxx). And a table criteria on UDxx would be used to limit the date range of your report.

I’d put all dates in the UD table then use a function (WEEKDAY()??) to filter out Sat and Sun. Because if you ever wanted to include them you would just tweak the BAQ, and not have to update the UD table.

[Adrian_Mepham]

Hi Calvin,
Thanks for that idea, it sounds good. Just a couple of questions. I am planning to have all dates for the last two years in the table, I can paste them in from Excel, but how do I then keep the table up to date? Do I re generate the table somehow when I run the BAQ?
Thanks again
Adrian.

[ckrusen (Calvin Krusen)]

I would paste every date from the oldest date tran date in your current DB to Today plus 10 years.

Alternate solution

Also, I thought of an even “hackier” solution. It assumes you have an existing table with sequentially numbered records like PartTran. The table must also never allow deleteing records (don’t want any gaps in the sequential records).

For example, PartTran.TranNum is sequential with no repeats and no deletions. Well you can delete, but only old records we’d not use in this hack.

First find the max of PartTran.TranNum. Doesn’t matter what it’s value is. This will be the relative number for the End date of your query range.

Say your date range is 1/1/2020 - 1/31/2020. This is 31 days, and requires 31 sequential numbers.
A sub query of PartTran, where
(TranNum =< Max(PartTran.TranNum))
AND (Max(PartTran.TranNum) - (DateDiff(@EndDate, @StartDate)))
And returns a calculated field of type Date, with the expression
DateDiff(@EndDate, (Max(PartTran.TranNum) - PartTran.TranNum) )

That sub-query gives you 31 records. with 31 dates from @StartDate to @EndDate. For example:

TranNum   Max(TranNum)  Max(Tr) - Trn    CalcDate
========  ============  ==============   ==========
453219       453219     0                01/31/2020
453218       453219     1                01/30/2020
453217       453219     2                01/29/2020
...
453190       453219     29               01/02/2020
453191       453219     30               01/01/2020

[Adrian_Mepham]

Hi Calvin, I tried this but I must be doing something wrong. I am getting an error saying;
“An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.”
This is what I have done;

Display the calculated field, one column;

The query looks like this;

Thanks for your help
Adrian.

[ckrusen (Calvin Krusen)]

I guess that would need to be a sub-sub-query.
Query 3:

• Only PartTran table
• no table criteria
• display cols:
  • Company
  • MaxTran = MAX(TranNum)

Query 2

• Tables PartTran and SubQuery3
• Relationship PartTran.Company = SubQuery3.Company
• Display Cols:
  • Company
  • CalcDate = dateadd(day, PartTran1.TranNum - SubQuery3.Calculated_MaxTran, @EndDate)
• Sub-Query Criteria:
  • Calculated CalcDate >= @StartDate
  • Calculated CalcDate <= @EndDate

Query1

• Tables: SubQuery2 (as the first table). Link your other tables to this one, via the CalcDate (I.E. SubQuery2.CalcDate = OrderHed.OrderDate (Left OUTER) )

[ckrusen (Calvin Krusen)]

Also - I should have used DateAdd(), not DateDiff()

[Adrian_Mepham]

Thanks Calvin, I will give that a go and let you know how I get on

[Adrian_Mepham]

Success!
image|105x251
Much appreciated Calvin, thank you
Adrian

[ckrusen (Calvin Krusen)]

I came up with a much better way (at least I think it’s better)

Make a sub query with the PartTran table in it. Any table will do, as long as there are more records in the table than dates you need.

Add two Calculated fields

• RowNum (int) = ROW_NUMBER() OVER (Order By PartTran.TranNum)
• Dt (date) = TOP (365*10) DateAdd(day, RowNum-(5*365), GETDATE())

This subquery will create a “temp table” with 10 years worth of dates. From 5 years ago to 5 years from now.

You can then use this SubQ in the top level one, and apply your date range criteria to the “table” criteria.

How it works…

The Dt column use the TOP xxx instruction to limit the number of rows from PartTran to return. The rows selected don’t matter. We just need 3650 records. The Dt expression uses the RowNum field to do the calculation to determine the dates of the 3650 rows

I used RowNum-(5*365) to make the 5 year offset.

My first solution using Max(TranNum) and subtracting the Current TranNum from it, has a potential issue if there are gaps in the TranNum.

[Adrian_Mepham]

Hi Calvin, I am getting an error message which says 'Incorrect syntax near the keyword ‘TOP’.

I had a look on google and the statement looks ok so I’m wondering if it is a bit of a red herring and the error is elsewhere?

[Cycl3 (CL Yuen)]

Set the TOP value at subquery options, not the field
it specifies how many records to return

[Adrian_Mepham]

Thank you @Cycl3

[ckrusen (Calvin Krusen)]

@Cycl3

Set the TOP value at subquery options

I hadn’t thought of that… I was originally trying to make the value (for the number of Top records to return) dynamic. When this failed, I just put in a constant in the calc field, instead of a formula.

But I’m still curious as to why why it worked in my BAQ but not in @Adrian_Mepham’s. Perhaps that can only be used if the SubQuery is the top Level query, and not an inner subquery.