BAQ Date Difference?

Unfortunately, this is the solution that works the best on most things that don't seem to be working right in Vantage.

I know of some apps that have very reproducable "issues", to pthe point where starting from scratch is usually easier that trying to edit and existig one.

Off the top of my head, I run into problems in:

1. BAQ's displayed fields in BAQs (display order & calculated fields)
2. Menu Security - Using groups in Allowed and Disallowed Access results in displaying the group ID instead of the group name, and then babalks at the fact the the group's ID isn't in the list of group names.

Just my $0.02

Calvin

--- In vantage@yahoogroups.com, Dan Godfrey <dgodfrey@...> wrote:
>
> Vic, It did work, but for some reason Vantage was in a funk and would not validate it. So once I blew it away and re-created the calc field then it worked.
>
>
>
Has anyone attempted to do a date difference between two dates IN A BAQ? If so can you show me how you did it?

Dan
Doe this help?

if (ShipHead.ShipDate - OrderRel.ReqDate) < 0 then 1 else 0

Ross

--- In vantage@yahoogroups.com, "dgodfrey_amc" <dgodfrey_amc@...> wrote:
>
> Has anyone attempted to do a date difference between two dates IN A BAQ? If so can you show me how you did it?
>
> Dan
>
Appreciate the thought but it is not working. Any other ideas?

________________________________
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of rossh777
Sent: Wednesday, January 04, 2012 11:43 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: BAQ Date Difference?



Doe this help?

if (ShipHead.ShipDate - OrderRel.ReqDate) < 0 then 1 else 0

Ross

--- In vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>, "dgodfrey_amc" <dgodfrey_amc@...> wrote:
>
> Has anyone attempted to do a date difference between two dates IN A BAQ? If so can you show me how you did it?
>
> Dan
>





[Non-text portions of this message have been removed]
Yes. You’re probably overthinking it as I was at first. It’s easy.

Create a Calc field in your BAQ. I’ll use JobHead as an example. The expression would be “JobHead.DueDate – JobHead.ReqDueDate”

Set the data type of the Calc field to Integer.

It returns the difference in days.



From: dgodfrey_amc
Sent: Wednesday, January 04, 2012 2:29 PM
To: vantage@yahoogroups.com
Subject: [Vantage] BAQ Date Difference?


Has anyone attempted to do a date difference between two dates IN A BAQ? If so can you show me how you did it?

Dan




No virus found in this message.
Checked by AVG - www.avg.com
Version: 2012.0.1901 / Virus Database: 2109/4722 - Release Date: 01/04/12


[Non-text portions of this message have been removed]
I thought that at first. here is my Calc field:

OrderDtl.NeedByDate - OrderDtl.RequestDate

It is still not working.

________________________________
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Vic Drecchio
Sent: Wednesday, January 04, 2012 11:47 AM
To: vantage@yahoogroups.com
Subject: Re: [Vantage] BAQ Date Difference?



Yes. You're probably overthinking it as I was at first. It's easy.

Create a Calc field in your BAQ. I'll use JobHead as an example. The expression would be "JobHead.DueDate - JobHead.ReqDueDate"

Set the data type of the Calc field to Integer.

It returns the difference in days.

From: dgodfrey_amc
Sent: Wednesday, January 04, 2012 2:29 PM
To: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>
Subject: [Vantage] BAQ Date Difference?

Has anyone attempted to do a date difference between two dates IN A BAQ? If so can you show me how you did it?

Dan

No virus found in this message.
Checked by AVG - www.avg.com
Version: 2012.0.1901 / Virus Database: 2109/4722 - Release Date: 01/04/12

[Non-text portions of this message have been removed]





[Non-text portions of this message have been removed]
This wan't just a thought, but a working calculated field from a query.

Here is another field from a Customer Shipment dashboard that provides days late:

if (ShipHead.ShipDate - OrderRel.ReqDate) > 0 then
(integer(ShipHead.ShipDate) - integer(OrderRel.ReqDate)) else 0

The data type is set to integer.

Ross

--- In vantage@yahoogroups.com, Dan Godfrey <dgodfrey@...> wrote:
>
> Appreciate the thought but it is not working. Any other ideas?
>
> ________________________________
> From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of rossh777
> Sent: Wednesday, January 04, 2012 11:43 AM
> To: vantage@yahoogroups.com
> Subject: [Vantage] Re: BAQ Date Difference?
>
>
>
> Doe this help?
>
> if (ShipHead.ShipDate - OrderRel.ReqDate) < 0 then 1 else 0
>
> Ross
>
> --- In vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>, "dgodfrey_amc" <dgodfrey_amc@> wrote:
> >
> > Has anyone attempted to do a date difference between two dates IN A BAQ? If so can you show me how you did it?
> >
> > Dan
> >
>
>
>
>
>
> [Non-text portions of this message have been removed]
>
Seems simple enough...try a new query from scratch and just add the OrderDtl table and display Ship , Need, Order and Diff Date...see what happens.
Criteria : Open Line = True; OrderLine = 3 (just to filter out data)

In your calculated data, in the editor, are the field names italic and highlighted? Or did you just type them out?

Field Name: DiffDate
Data Type: Integer
Format: ->>,>>>,>>9
Label: Diff Date

Editor:
OrderDtl.NeedByDate - OrderDtl.RequestDate

Results:
Ship By Need By Order Diff Date
7/25/2005 12:00:00 AM 7/25/2005 12:00:00 AM 231363 0
11/1/2005 12:00:00 AM 11/1/2005 12:00:00 AM 232150 0
9/20/2006 12:00:00 AM 9/22/2006 12:00:00 AM 234248 2
3/22/2007 12:00:00 AM 3/22/2007 12:00:00 AM 236157 0
9/25/2008 12:00:00 AM 9/30/2008 12:00:00 AM 241580 5
8/5/2015 12:00:00 AM 8/5/2015 12:00:00 AM 258724 0
1/5/2012 12:00:00 AM 1/10/2012 12:00:00 AM 259317 5
1/3/2012 12:00:00 AM 1/10/2012 12:00:00 AM 259320 7
1/3/2012 12:00:00 AM 1/10/2012 12:00:00 AM 259321 7
10/10/2013 12:00:00 AM 1/11/2012 12:00:00 AM 262165 -638

Miguel Santillan
ERP Administrator
Compass Manufacturing Services
DL: 510-661-6666

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of rossh777
Sent: Wednesday, January 04, 2012 12:03 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: BAQ Date Difference?



This wan't just a thought, but a working calculated field from a query.

Here is another field from a Customer Shipment dashboard that provides days late:

if (ShipHead.ShipDate - OrderRel.ReqDate) > 0 then
(integer(ShipHead.ShipDate) - integer(OrderRel.ReqDate)) else 0

The data type is set to integer.

Ross

--- In vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>, Dan Godfrey <dgodfrey@...<mailto:dgodfrey@...>> wrote:
>
> Appreciate the thought but it is not working. Any other ideas?
>
> ________________________________
> From: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>] On Behalf Of rossh777
> Sent: Wednesday, January 04, 2012 11:43 AM
> To: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>
> Subject: [Vantage] Re: BAQ Date Difference?
>
>
>
> Doe this help?
>
> if (ShipHead.ShipDate - OrderRel.ReqDate) < 0 then 1 else 0
>
> Ross
>
> --- In vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com>, "dgodfrey_amc" <dgodfrey_amc@> wrote:
> >
> > Has anyone attempted to do a date difference between two dates IN A BAQ? If so can you show me how you did it?
> >
> > Dan
> >
>
>
>
>
>
> [Non-text portions of this message have been removed]
>



[Non-text portions of this message have been removed]
I don't know why it is not working for me then.

________________________________
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of rossh777
Sent: Wednesday, January 04, 2012 12:03 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: BAQ Date Difference?



This wan't just a thought, but a working calculated field from a query.

Here is another field from a Customer Shipment dashboard that provides days late:

if (ShipHead.ShipDate - OrderRel.ReqDate) > 0 then
(integer(ShipHead.ShipDate) - integer(OrderRel.ReqDate)) else 0

The data type is set to integer.

Ross

--- In vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>, Dan Godfrey <dgodfrey@...> wrote:
>
> Appreciate the thought but it is not working. Any other ideas?
>
> ________________________________
> From: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>] On Behalf Of rossh777
> Sent: Wednesday, January 04, 2012 11:43 AM
> To: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>
> Subject: [Vantage] Re: BAQ Date Difference?
>
>
>
> Doe this help?
>
> if (ShipHead.ShipDate - OrderRel.ReqDate) < 0 then 1 else 0
>
> Ross
>
> --- In vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com>, "dgodfrey_amc" <dgodfrey_amc@> wrote:
> >
> > Has anyone attempted to do a date difference between two dates IN A BAQ? If so can you show me how you did it?
> >
> > Dan
> >
>
>
>
>
>
> [Non-text portions of this message have been removed]
>





[Non-text portions of this message have been removed]
Thanks everyone. It wasn't working so I followed the last advice to redo the the calc field and guess what, the same formula worked.

Dan

________________________________
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Miguel Santillan
Sent: Wednesday, January 04, 2012 12:17 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Re: BAQ Date Difference?



Seems simple enough...try a new query from scratch and just add the OrderDtl table and display Ship , Need, Order and Diff Date...see what happens.
Criteria : Open Line = True; OrderLine = 3 (just to filter out data)

In your calculated data, in the editor, are the field names italic and highlighted? Or did you just type them out?

Field Name: DiffDate
Data Type: Integer
Format: ->>,>>>,>>9
Label: Diff Date

Editor:
OrderDtl.NeedByDate - OrderDtl.RequestDate

Results:
Ship By Need By Order Diff Date
7/25/2005 12:00:00 AM 7/25/2005 12:00:00 AM 231363 0
11/1/2005 12:00:00 AM 11/1/2005 12:00:00 AM 232150 0
9/20/2006 12:00:00 AM 9/22/2006 12:00:00 AM 234248 2
3/22/2007 12:00:00 AM 3/22/2007 12:00:00 AM 236157 0
9/25/2008 12:00:00 AM 9/30/2008 12:00:00 AM 241580 5
8/5/2015 12:00:00 AM 8/5/2015 12:00:00 AM 258724 0
1/5/2012 12:00:00 AM 1/10/2012 12:00:00 AM 259317 5
1/3/2012 12:00:00 AM 1/10/2012 12:00:00 AM 259320 7
1/3/2012 12:00:00 AM 1/10/2012 12:00:00 AM 259321 7
10/10/2013 12:00:00 AM 1/11/2012 12:00:00 AM 262165 -638

Miguel Santillan
ERP Administrator
Compass Manufacturing Services
DL: 510-661-6666

From: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>] On Behalf Of rossh777
Sent: Wednesday, January 04, 2012 12:03 PM
To: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>
Subject: [Vantage] Re: BAQ Date Difference?

This wan't just a thought, but a working calculated field from a query.

Here is another field from a Customer Shipment dashboard that provides days late:

if (ShipHead.ShipDate - OrderRel.ReqDate) > 0 then
(integer(ShipHead.ShipDate) - integer(OrderRel.ReqDate)) else 0

The data type is set to integer.

Ross

--- In vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com>, Dan Godfrey <dgodfrey@...<mailto:dgodfrey@...>> wrote:
>
> Appreciate the thought but it is not working. Any other ideas?
>
> ________________________________
> From: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com>] On Behalf Of rossh777
> Sent: Wednesday, January 04, 2012 11:43 AM
> To: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com>
> Subject: [Vantage] Re: BAQ Date Difference?
>
>
>
> Doe this help?
>
> if (ShipHead.ShipDate - OrderRel.ReqDate) < 0 then 1 else 0
>
> Ross
>
> --- In vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com>, "dgodfrey_amc" <dgodfrey_amc@> wrote:
> >
> > Has anyone attempted to do a date difference between two dates IN A BAQ? If so can you show me how you did it?
> >
> > Dan
> >
>
>
>
>
>
> [Non-text portions of this message have been removed]
>

[Non-text portions of this message have been removed]





[Non-text portions of this message have been removed]
Vic, It did work, but for some reason Vantage was in a funk and would not validate it. So once I blew it away and re-created the calc field then it worked.



________________________________
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Dan Godfrey
Sent: Wednesday, January 04, 2012 12:21 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Re: BAQ Date Difference?



Thanks everyone. It wasn't working so I followed the last advice to redo the the calc field and guess what, the same formula worked.

Dan

________________________________
From: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>] On Behalf Of Miguel Santillan
Sent: Wednesday, January 04, 2012 12:17 PM
To: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>
Subject: RE: [Vantage] Re: BAQ Date Difference?

Seems simple enough...try a new query from scratch and just add the OrderDtl table and display Ship , Need, Order and Diff Date...see what happens.
Criteria : Open Line = True; OrderLine = 3 (just to filter out data)

In your calculated data, in the editor, are the field names italic and highlighted? Or did you just type them out?

Field Name: DiffDate
Data Type: Integer
Format: ->>,>>>,>>9
Label: Diff Date

Editor:
OrderDtl.NeedByDate - OrderDtl.RequestDate

Results:
Ship By Need By Order Diff Date
7/25/2005 12:00:00 AM 7/25/2005 12:00:00 AM 231363 0
11/1/2005 12:00:00 AM 11/1/2005 12:00:00 AM 232150 0
9/20/2006 12:00:00 AM 9/22/2006 12:00:00 AM 234248 2
3/22/2007 12:00:00 AM 3/22/2007 12:00:00 AM 236157 0
9/25/2008 12:00:00 AM 9/30/2008 12:00:00 AM 241580 5
8/5/2015 12:00:00 AM 8/5/2015 12:00:00 AM 258724 0
1/5/2012 12:00:00 AM 1/10/2012 12:00:00 AM 259317 5
1/3/2012 12:00:00 AM 1/10/2012 12:00:00 AM 259320 7
1/3/2012 12:00:00 AM 1/10/2012 12:00:00 AM 259321 7
10/10/2013 12:00:00 AM 1/11/2012 12:00:00 AM 262165 -638

Miguel Santillan
ERP Administrator
Compass Manufacturing Services
DL: 510-661-6666

From: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com>] On Behalf Of rossh777
Sent: Wednesday, January 04, 2012 12:03 PM
To: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com>
Subject: [Vantage] Re: BAQ Date Difference?

This wan't just a thought, but a working calculated field from a query.

Here is another field from a Customer Shipment dashboard that provides days late:

if (ShipHead.ShipDate - OrderRel.ReqDate) > 0 then
(integer(ShipHead.ShipDate) - integer(OrderRel.ReqDate)) else 0

The data type is set to integer.

Ross

--- In vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com>, Dan Godfrey <dgodfrey@...<mailto:dgodfrey@...>> wrote:
>
> Appreciate the thought but it is not working. Any other ideas?
>
> ________________________________
> From: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com>] On Behalf Of rossh777
> Sent: Wednesday, January 04, 2012 11:43 AM
> To: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com>
> Subject: [Vantage] Re: BAQ Date Difference?
>
>
>
> Doe this help?
>
> if (ShipHead.ShipDate - OrderRel.ReqDate) < 0 then 1 else 0
>
> Ross
>
> --- In vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com>, "dgodfrey_amc" <dgodfrey_amc@> wrote:
> >
> > Has anyone attempted to do a date difference between two dates IN A BAQ? If so can you show me how you did it?
> >
> > Dan
> >
>
>
>
>
>
> [Non-text portions of this message have been removed]
>

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]





[Non-text portions of this message have been removed]