# BAQ YTD calculation

I've found that setting up a date range in the dashboard is easier that trying to calculate the dates in the query themselves. You will also have a little more functionality/flexibility too so that you are not tied into year. You could do months or whatever the user wanted. Below is a link to the ERP user guide that showed me how to add ranges in the search functions on trackers. It's e-10 and I don't know how different e-9 so maybe it's not helpful. (you'll need the epic web password to view the link)

Epicor 9.05.702A

has anyone created a calculation in a BAQ for YTD and LY (last year)?  I need to calculate how much we paid Vendors.

Thanks Kim

Link in the table that has the numbers you want to total, set it to summary (right click on table).

In a mix of progress and pseudo code.  You can use the String() function to extract the year portion of a date.

Create calculated field for YTD

If(some.FieldDate() = this year) then total(fieldyouwanttototal)) else 0

Create calculated field for LY

If(some.FieldDate() = last year) then total(fieldyouwanttototal)) else 0

Using prompts/parameters you can ask the user for the year they wish to use as "YTD" and then subtract 1 to get the previous year.  If it will always be the current year you can use the Today function to get the year.  I think there may be a BAQ constant that has the year in it also.

Jim Kinneman
Encompass Solutions, Inc
Ok, so I was thinking about this options here and thought well maybe I will just prompt them to eter a date but then I wasn't sure how that would pull for LY info too.  In the info you gave me to write a calculation can a TODAY be used instead of a date field.  I will not have a date field to go off of, because I am Totaling ALL invoices for that customer.

Did I make sense?

Thanks Kim

On Tuesday, May 24, 2016 12:42 PM, "jckinneman@... [vantage]" <vantage@yahoogroups.com> wrote:

``````<div id="ygrps-yiv-1376133426yiv8734089169ygrp-text">

<div>Link in the table that has the numbers you want to total, set it to summary (right click on table).</div><div><br clear="none"></div><div>In a mix of progress and pseudo code. &nbsp;You can use the String() function to extract the year portion of a date.<br clear="none"><div><br clear="none"></div><div>Create calculated field for YTD</div><div><br clear="none"></div><div>If(some.FieldDate() = this year) then total(fieldyouwanttototal)) else 0</div><div><br clear="none"></div><div>Create calculated field for LY</div><div><br clear="none"></div><div><div>If(some.FieldDate() = last year) then total(fieldyouwanttototal)) else 0</div><div><br clear="none"></div><div>Using prompts/parameters you can ask the user for the year they wish to use as &quot;YTD&quot; and then subtract 1 to get the previous year. &nbsp;If it will always be the current year you can use the Today function to get the year. &nbsp;I think there may be a BAQ constant that has the year in it also.</div><div><br clear="none"></div><div>Jim Kinneman</div><div>Encompass Solutions, Inc</div></div></div>

</div>

<div style="color:#fff;height:0;"></div></div>
``````
#ygrps-yiv-1376133426 #ygrps-yiv-1376133426yiv8734089169 #ygrps-yiv-1376133426yiv8734089169 -- #ygrps-yiv-1376133426yiv8734089169ygrp-mkp { border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}

#ygrps-yiv-1376133426 #ygrps-yiv-1376133426yiv8734089169 #ygrps-yiv-1376133426yiv8734089169ygrp-mkp hr {
border:1px solid #d8d8d8;}

#ygrps-yiv-1376133426 #ygrps-yiv-1376133426yiv8734089169 #ygrps-yiv-1376133426yiv8734089169ygrp-mkp #ygrps-yiv-1376133426yiv8734089169hd {
color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}

margin-bottom:10px;}

#ygrps-yiv-1376133426 #ygrps-yiv-1376133426yiv8734089169 #ygrps-yiv-1376133426yiv8734089169ygrp-mkp .ygrps-yiv-1376133426yiv8734089169ad p {
margin:0;}

#ygrps-yiv-1376133426 #ygrps-yiv-1376133426yiv8734089169 #ygrps-yiv-1376133426yiv8734089169ygrp-mkp .ygrps-yiv-1376133426yiv8734089169ad a {
color:#0000ff;text-decoration:none;}
font-family:Arial;}

#ygrps-yiv-1376133426 #ygrps-yiv-1376133426yiv8734089169 #ygrps-yiv-1376133426yiv8734089169ygrp-sponsor #ygrps-yiv-1376133426yiv8734089169ygrp-lc #ygrps-yiv-1376133426yiv8734089169hd {
margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}

#ygrps-yiv-1376133426 #ygrps-yiv-1376133426yiv8734089169 #ygrps-yiv-1376133426yiv8734089169actions {

#ygrps-yiv-1376133426 #ygrps-yiv-1376133426yiv8734089169 #ygrps-yiv-1376133426yiv8734089169activity {

#ygrps-yiv-1376133426 #ygrps-yiv-1376133426yiv8734089169 #ygrps-yiv-1376133426yiv8734089169activity span {
font-weight:700;}

#ygrps-yiv-1376133426 #ygrps-yiv-1376133426yiv8734089169 #ygrps-yiv-1376133426yiv8734089169activity span:first-child {
text-transform:uppercase;}

#ygrps-yiv-1376133426 #ygrps-yiv-1376133426yiv8734089169 #ygrps-yiv-1376133426yiv8734089169activity span a {
color:#5085b6;text-decoration:none;}

#ygrps-yiv-1376133426 #ygrps-yiv-1376133426yiv8734089169 #ygrps-yiv-1376133426yiv8734089169activity span span {
color:#ff7900;}

#ygrps-yiv-1376133426 #ygrps-yiv-1376133426yiv8734089169 #ygrps-yiv-1376133426yiv8734089169activity span .ygrps-yiv-1376133426yiv8734089169underline {
text-decoration:underline;}

#ygrps-yiv-1376133426 #ygrps-yiv-1376133426yiv8734089169 .ygrps-yiv-1376133426yiv8734089169attach {

#ygrps-yiv-1376133426 #ygrps-yiv-1376133426yiv8734089169 .ygrps-yiv-1376133426yiv8734089169attach div a {
text-decoration:none;}

#ygrps-yiv-1376133426 #ygrps-yiv-1376133426yiv8734089169 .ygrps-yiv-1376133426yiv8734089169attach img {

#ygrps-yiv-1376133426 #ygrps-yiv-1376133426yiv8734089169 .ygrps-yiv-1376133426yiv8734089169attach label {
display:block;margin-bottom:5px;}

#ygrps-yiv-1376133426 #ygrps-yiv-1376133426yiv8734089169 .ygrps-yiv-1376133426yiv8734089169attach label a {
text-decoration:none;}

#ygrps-yiv-1376133426 #ygrps-yiv-1376133426yiv8734089169 blockquote {
margin:0 0 0 4px;}

#ygrps-yiv-1376133426 #ygrps-yiv-1376133426yiv8734089169 .ygrps-yiv-1376133426yiv8734089169bold {
font-family:Arial;font-size:13px;font-weight:700;}

#ygrps-yiv-1376133426 #ygrps-yiv-1376133426yiv8734089169 .ygrps-yiv-1376133426yiv8734089169bold a {
text-decoration:none;}

#ygrps-yiv-1376133426 #ygrps-yiv-1376133426yiv8734089169 dd.ygrps-yiv-1376133426yiv8734089169last p a {
font-family:Verdana;font-weight:700;}

#ygrps-yiv-1376133426 #ygrps-yiv-1376133426yiv8734089169 dd.ygrps-yiv-1376133426yiv8734089169last p span {
margin-right:10px;font-family:Verdana;font-weight:700;}

#ygrps-yiv-1376133426 #ygrps-yiv-1376133426yiv8734089169 dd.ygrps-yiv-1376133426yiv8734089169last p span.ygrps-yiv-1376133426yiv8734089169yshortcuts {
margin-right:0;}

#ygrps-yiv-1376133426 #ygrps-yiv-1376133426yiv8734089169 div.ygrps-yiv-1376133426yiv8734089169attach-table div div a {
text-decoration:none;}

#ygrps-yiv-1376133426 #ygrps-yiv-1376133426yiv8734089169 div.ygrps-yiv-1376133426yiv8734089169attach-table {
width:400px;}

#ygrps-yiv-1376133426 #ygrps-yiv-1376133426yiv8734089169 div.ygrps-yiv-1376133426yiv8734089169file-title a, #ygrps-yiv-1376133426 #ygrps-yiv-1376133426yiv8734089169 div.ygrps-yiv-1376133426yiv8734089169file-title a:active, #ygrps-yiv-1376133426 #ygrps-yiv-1376133426yiv8734089169 div.ygrps-yiv-1376133426yiv8734089169file-title a:hover, #ygrps-yiv-1376133426 #ygrps-yiv-1376133426yiv8734089169 div.ygrps-yiv-1376133426yiv8734089169file-title a:visited {
text-decoration:none;}

#ygrps-yiv-1376133426 #ygrps-yiv-1376133426yiv8734089169 div.ygrps-yiv-1376133426yiv8734089169photo-title a, #ygrps-yiv-1376133426 #ygrps-yiv-1376133426yiv8734089169 div.ygrps-yiv-1376133426yiv8734089169photo-title a:active, #ygrps-yiv-1376133426 #ygrps-yiv-1376133426yiv8734089169 div.ygrps-yiv-1376133426yiv8734089169photo-title a:hover, #ygrps-yiv-1376133426 #ygrps-yiv-1376133426yiv8734089169 div.ygrps-yiv-1376133426yiv8734089169photo-title a:visited {
text-decoration:none;}

#ygrps-yiv-1376133426 #ygrps-yiv-1376133426yiv8734089169 div#ygrps-yiv-1376133426yiv8734089169ygrp-mlmsg #ygrps-yiv-1376133426yiv8734089169ygrp-msg p a span.ygrps-yiv-1376133426yiv8734089169yshortcuts {
font-family:Verdana;font-size:10px;font-weight:normal;}

#ygrps-yiv-1376133426 #ygrps-yiv-1376133426yiv8734089169 .ygrps-yiv-1376133426yiv8734089169green {
color:#628c2a;}

#ygrps-yiv-1376133426 #ygrps-yiv-1376133426yiv8734089169 .ygrps-yiv-1376133426yiv8734089169MsoNormal {
margin:0 0 0 0;}

#ygrps-yiv-1376133426 #ygrps-yiv-1376133426yiv8734089169 o {
font-size:0;}

#ygrps-yiv-1376133426 #ygrps-yiv-1376133426yiv8734089169 #ygrps-yiv-1376133426yiv8734089169photos div {
float:left;width:72px;}

#ygrps-yiv-1376133426 #ygrps-yiv-1376133426yiv8734089169 #ygrps-yiv-1376133426yiv8734089169photos div div {
border:1px solid #666666;height:62px;overflow:hidden;width:62px;}

#ygrps-yiv-1376133426 #ygrps-yiv-1376133426yiv8734089169 #ygrps-yiv-1376133426yiv8734089169photos div label {
color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}

#ygrps-yiv-1376133426 #ygrps-yiv-1376133426yiv8734089169 #ygrps-yiv-1376133426yiv8734089169reco-category {
font-size:77%;}

#ygrps-yiv-1376133426 #ygrps-yiv-1376133426yiv8734089169 #ygrps-yiv-1376133426yiv8734089169reco-desc {
font-size:77%;}

#ygrps-yiv-1376133426 #ygrps-yiv-1376133426yiv8734089169 .ygrps-yiv-1376133426yiv8734089169replbq {
margin:4px;}

#ygrps-yiv-1376133426 #ygrps-yiv-1376133426yiv8734089169 #ygrps-yiv-1376133426yiv8734089169ygrp-actbar div a:first-child {

#ygrps-yiv-1376133426 #ygrps-yiv-1376133426yiv8734089169 #ygrps-yiv-1376133426yiv8734089169ygrp-mlmsg {
font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}

#ygrps-yiv-1376133426 #ygrps-yiv-1376133426yiv8734089169 #ygrps-yiv-1376133426yiv8734089169ygrp-mlmsg table {
font-size:inherit;font:100%;}

#ygrps-yiv-1376133426 #ygrps-yiv-1376133426yiv8734089169 #ygrps-yiv-1376133426yiv8734089169ygrp-mlmsg select, #ygrps-yiv-1376133426 #ygrps-yiv-1376133426yiv8734089169 input, #ygrps-yiv-1376133426 #ygrps-yiv-1376133426yiv8734089169 textarea {
font:99% Arial, Helvetica, clean, sans-serif;}

#ygrps-yiv-1376133426 #ygrps-yiv-1376133426yiv8734089169 #ygrps-yiv-1376133426yiv8734089169ygrp-mlmsg pre, #ygrps-yiv-1376133426 #ygrps-yiv-1376133426yiv8734089169 code {
font:115% monospace;}

#ygrps-yiv-1376133426 #ygrps-yiv-1376133426yiv8734089169 #ygrps-yiv-1376133426yiv8734089169ygrp-mlmsg * {
line-height:1.22em;}

#ygrps-yiv-1376133426 #ygrps-yiv-1376133426yiv8734089169 #ygrps-yiv-1376133426yiv8734089169ygrp-mlmsg #ygrps-yiv-1376133426yiv8734089169logo {

#ygrps-yiv-1376133426 #ygrps-yiv-1376133426yiv8734089169 #ygrps-yiv-1376133426yiv8734089169ygrp-msg p a {
font-family:Verdana;}

#ygrps-yiv-1376133426 #ygrps-yiv-1376133426yiv8734089169 #ygrps-yiv-1376133426yiv8734089169ygrp-msg p#ygrps-yiv-1376133426yiv8734089169attach-count span {
color:#1E66AE;font-weight:700;}

color:#ff7900;font-weight:700;}

#ygrps-yiv-1376133426 #ygrps-yiv-1376133426yiv8734089169 #ygrps-yiv-1376133426yiv8734089169ygrp-reco {

#ygrps-yiv-1376133426 #ygrps-yiv-1376133426yiv8734089169 #ygrps-yiv-1376133426yiv8734089169ygrp-sponsor #ygrps-yiv-1376133426yiv8734089169ov li a {
font-size:130%;text-decoration:none;}

#ygrps-yiv-1376133426 #ygrps-yiv-1376133426yiv8734089169 #ygrps-yiv-1376133426yiv8734089169ygrp-sponsor #ygrps-yiv-1376133426yiv8734089169ov li {

#ygrps-yiv-1376133426 #ygrps-yiv-1376133426yiv8734089169 #ygrps-yiv-1376133426yiv8734089169ygrp-sponsor #ygrps-yiv-1376133426yiv8734089169ov ul {

#ygrps-yiv-1376133426 #ygrps-yiv-1376133426yiv8734089169 #ygrps-yiv-1376133426yiv8734089169ygrp-text {
font-family:Georgia;}

#ygrps-yiv-1376133426 #ygrps-yiv-1376133426yiv8734089169 #ygrps-yiv-1376133426yiv8734089169ygrp-text p {
margin:0 0 1em 0;}

#ygrps-yiv-1376133426 #ygrps-yiv-1376133426yiv8734089169 #ygrps-yiv-1376133426yiv8734089169ygrp-text tt {
font-size:120%;}

#ygrps-yiv-1376133426 #ygrps-yiv-1376133426yiv8734089169 #ygrps-yiv-1376133426yiv8734089169ygrp-vital ul li:last-child {
border-right:none !important;}
#ygrps-yiv-1376133426

Kim,

You have me a little confused or I am misreading the context of what you refer to as a date field.

You need a date field to determine if the invoice is in this year or last year.  "invoice" would be the table you are summarizing, not in a system right now to get the exact name for the invoice header table.

Calculated YTD Field
define variable thisYear as integer.
thisYear = year(today).
if invoice.date = thisYear then Total(invoice.somenumber) else total(0).

Calculated LY Field
define variable lastyear as integer.
lastyear = year(today) -1
if invoice.date = lastYear then Total(invoice.somenumber) else total(0).

Jim Kinneman
Encompass Solutions, Inc.
I probably confused you.

Yes, I have the APInvHed table as Summarize.

I am following you but I just don't grasp how to get the year.  Sorry not programmer by trade....learning still.  This is what I have.

if year (APInvHed.InvoiceDate) = thisYear then Total(APInvHed.APInvoiceAmt) else total(0).  (Error: There is a syntaz or data type conversion error in "YTD' calculated field expression.

Aimee helped me and said Hey it is Jim...LOL

Kim,

Had a chance to get into a 9.05 system.  There were a few attempts that should have been syntactically correct but the calculated field editor complained.

Linking ApInvHed to Vendor (vendor is parent).  Set ApInvHed  to summary.  You can also make the join an outer join to get all vendors regardless of activity.  And inner join would only show vendors with invoice activity.

This works for:

YearToDate
total(if Year(APInvHed.InvoiceDate) = year(today)  then APInvHed.InvoiceAmt else 0)

LastYear
total(if Year(APInvHed.InvoiceDate) = year(today) - 1 then APInvHed.InvoiceAmt else 0)

Jim Kinneman
Encompass Solutions, Inc

Worked...i have a big BUT question.

If I want a dashboard to have a date range it won't work with this calculation code correct?  I know how to change it for work for the YTD but the LY would not show correctly with that change.

Thoughts??

Thanks kim

The date range needs to include the same range for last year in addition to this year's.  I'd have to think it through some but basically taking the date range and subtracting a year from it you could combine it so you would do something like this.

(Date >= startRange and Date <= endrange) or (Date >= startRange - year and Date <= EndRange - Year)

I did something like this once by breaking up the range into two, one for what you want the primary year to be and one for what you want to consider the secondary year.  In that case the client wanted to compare any two years not just the current and the last year.

Jim Kinneman
Encompass Solutions, Inc

So the following worked....

total(if Year(APInvHed.InvoiceDate) = year(today)  then APInvHed.InvoiceAmt else 0)

BUT then had to pull from the RcvDtl table and now I am getting the error,,,"There is a syntax or data type conversion error in "LY' calculated field expression"

total(if Year(Rcvdtl.ReceiptDate) = year(today)  then Rcvdtl.ourunitcost else 0)

On Friday, May 27, 2016 10:39 AM, "Kimberley White faepigrl@... [vantage]" <vantage@yahoogroups.com> wrote:

``````<div id="ygrps-yiv-104354898yiv2559859138ygrp-text">

<div></div><div style="color:#000;background-color:#fff;font-family:HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif;font-size:16px;"><div>Worked...i have a big BUT question.</div><div><br clear="none"></div><div dir="ltr" id="ygrps-yiv-104354898yiv2559859138yui_3_16_0_ym19_1_1464359626548_4537">If I want a dashboard to have a date range it won&#39;t work with this calculation code correct?&nbsp; I know how to change it for work for the YTD but the LY would not show correctly with that change.</div><div dir="ltr" id="ygrps-yiv-104354898yiv2559859138yui_3_16_0_ym19_1_1464359626548_4555"><br clear="none"></div><div dir="ltr" id="ygrps-yiv-104354898yiv2559859138yui_3_16_0_ym19_1_1464359626548_4556">Thoughts??</div><div dir="ltr" id="ygrps-yiv-104354898yiv2559859138yui_3_16_0_ym19_1_1464359626548_4557"><br clear="none"></div><div dir="ltr" id="ygrps-yiv-104354898yiv2559859138yui_3_16_0_ym19_1_1464359626548_4558">Thanks kim<br clear="none"></div><div id="ygrps-yiv-104354898yiv2559859138yui_3_16_0_ym19_1_1464359626548_4536"><br clear="none"></div></div>

</div>

<div style="color:#fff;height:0;"></div></div>
``````
#ygrps-yiv-104354898 #ygrps-yiv-104354898yiv2559859138 #ygrps-yiv-104354898yiv2559859138 -- #ygrps-yiv-104354898yiv2559859138ygrp-mkp { border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}

#ygrps-yiv-104354898 #ygrps-yiv-104354898yiv2559859138 #ygrps-yiv-104354898yiv2559859138ygrp-mkp hr {
border:1px solid #d8d8d8;}

#ygrps-yiv-104354898 #ygrps-yiv-104354898yiv2559859138 #ygrps-yiv-104354898yiv2559859138ygrp-mkp #ygrps-yiv-104354898yiv2559859138hd {
color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}

margin-bottom:10px;}

#ygrps-yiv-104354898 #ygrps-yiv-104354898yiv2559859138 #ygrps-yiv-104354898yiv2559859138ygrp-mkp .ygrps-yiv-104354898yiv2559859138ad p {
margin:0;}

#ygrps-yiv-104354898 #ygrps-yiv-104354898yiv2559859138 #ygrps-yiv-104354898yiv2559859138ygrp-mkp .ygrps-yiv-104354898yiv2559859138ad a {
color:#0000ff;text-decoration:none;}
font-family:Arial;}

#ygrps-yiv-104354898 #ygrps-yiv-104354898yiv2559859138 #ygrps-yiv-104354898yiv2559859138ygrp-sponsor #ygrps-yiv-104354898yiv2559859138ygrp-lc #ygrps-yiv-104354898yiv2559859138hd {
margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}

#ygrps-yiv-104354898 #ygrps-yiv-104354898yiv2559859138 #ygrps-yiv-104354898yiv2559859138actions {

#ygrps-yiv-104354898 #ygrps-yiv-104354898yiv2559859138 #ygrps-yiv-104354898yiv2559859138activity {

#ygrps-yiv-104354898 #ygrps-yiv-104354898yiv2559859138 #ygrps-yiv-104354898yiv2559859138activity span {
font-weight:700;}

#ygrps-yiv-104354898 #ygrps-yiv-104354898yiv2559859138 #ygrps-yiv-104354898yiv2559859138activity span:first-child {
text-transform:uppercase;}

#ygrps-yiv-104354898 #ygrps-yiv-104354898yiv2559859138 #ygrps-yiv-104354898yiv2559859138activity span a {
color:#5085b6;text-decoration:none;}

#ygrps-yiv-104354898 #ygrps-yiv-104354898yiv2559859138 #ygrps-yiv-104354898yiv2559859138activity span span {
color:#ff7900;}

#ygrps-yiv-104354898 #ygrps-yiv-104354898yiv2559859138 #ygrps-yiv-104354898yiv2559859138activity span .ygrps-yiv-104354898yiv2559859138underline {
text-decoration:underline;}

#ygrps-yiv-104354898 #ygrps-yiv-104354898yiv2559859138 .ygrps-yiv-104354898yiv2559859138attach {

#ygrps-yiv-104354898 #ygrps-yiv-104354898yiv2559859138 .ygrps-yiv-104354898yiv2559859138attach div a {
text-decoration:none;}

#ygrps-yiv-104354898 #ygrps-yiv-104354898yiv2559859138 .ygrps-yiv-104354898yiv2559859138attach img {

#ygrps-yiv-104354898 #ygrps-yiv-104354898yiv2559859138 .ygrps-yiv-104354898yiv2559859138attach label {
display:block;margin-bottom:5px;}

#ygrps-yiv-104354898 #ygrps-yiv-104354898yiv2559859138 .ygrps-yiv-104354898yiv2559859138attach label a {
text-decoration:none;}

#ygrps-yiv-104354898 #ygrps-yiv-104354898yiv2559859138 blockquote {
margin:0 0 0 4px;}

#ygrps-yiv-104354898 #ygrps-yiv-104354898yiv2559859138 .ygrps-yiv-104354898yiv2559859138bold {
font-family:Arial;font-size:13px;font-weight:700;}

#ygrps-yiv-104354898 #ygrps-yiv-104354898yiv2559859138 .ygrps-yiv-104354898yiv2559859138bold a {
text-decoration:none;}

#ygrps-yiv-104354898 #ygrps-yiv-104354898yiv2559859138 dd.ygrps-yiv-104354898yiv2559859138last p a {
font-family:Verdana;font-weight:700;}

#ygrps-yiv-104354898 #ygrps-yiv-104354898yiv2559859138 dd.ygrps-yiv-104354898yiv2559859138last p span {
margin-right:10px;font-family:Verdana;font-weight:700;}

#ygrps-yiv-104354898 #ygrps-yiv-104354898yiv2559859138 dd.ygrps-yiv-104354898yiv2559859138last p span.ygrps-yiv-104354898yiv2559859138yshortcuts {
margin-right:0;}

#ygrps-yiv-104354898 #ygrps-yiv-104354898yiv2559859138 div.ygrps-yiv-104354898yiv2559859138attach-table div div a {
text-decoration:none;}

#ygrps-yiv-104354898 #ygrps-yiv-104354898yiv2559859138 div.ygrps-yiv-104354898yiv2559859138attach-table {
width:400px;}

#ygrps-yiv-104354898 #ygrps-yiv-104354898yiv2559859138 div.ygrps-yiv-104354898yiv2559859138file-title a, #ygrps-yiv-104354898 #ygrps-yiv-104354898yiv2559859138 div.ygrps-yiv-104354898yiv2559859138file-title a:active, #ygrps-yiv-104354898 #ygrps-yiv-104354898yiv2559859138 div.ygrps-yiv-104354898yiv2559859138file-title a:hover, #ygrps-yiv-104354898 #ygrps-yiv-104354898yiv2559859138 div.ygrps-yiv-104354898yiv2559859138file-title a:visited {
text-decoration:none;}

#ygrps-yiv-104354898 #ygrps-yiv-104354898yiv2559859138 div.ygrps-yiv-104354898yiv2559859138photo-title a, #ygrps-yiv-104354898 #ygrps-yiv-104354898yiv2559859138 div.ygrps-yiv-104354898yiv2559859138photo-title a:active, #ygrps-yiv-104354898 #ygrps-yiv-104354898yiv2559859138 div.ygrps-yiv-104354898yiv2559859138photo-title a:hover, #ygrps-yiv-104354898 #ygrps-yiv-104354898yiv2559859138 div.ygrps-yiv-104354898yiv2559859138photo-title a:visited {
text-decoration:none;}

#ygrps-yiv-104354898 #ygrps-yiv-104354898yiv2559859138 div#ygrps-yiv-104354898yiv2559859138ygrp-mlmsg #ygrps-yiv-104354898yiv2559859138ygrp-msg p a span.ygrps-yiv-104354898yiv2559859138yshortcuts {
font-family:Verdana;font-size:10px;font-weight:normal;}

#ygrps-yiv-104354898 #ygrps-yiv-104354898yiv2559859138 .ygrps-yiv-104354898yiv2559859138green {
color:#628c2a;}

#ygrps-yiv-104354898 #ygrps-yiv-104354898yiv2559859138 .ygrps-yiv-104354898yiv2559859138MsoNormal {
margin:0 0 0 0;}

#ygrps-yiv-104354898 #ygrps-yiv-104354898yiv2559859138 o {
font-size:0;}

#ygrps-yiv-104354898 #ygrps-yiv-104354898yiv2559859138 #ygrps-yiv-104354898yiv2559859138photos div {
float:left;width:72px;}

#ygrps-yiv-104354898 #ygrps-yiv-104354898yiv2559859138 #ygrps-yiv-104354898yiv2559859138photos div div {
border:1px solid #666666;height:62px;overflow:hidden;width:62px;}

#ygrps-yiv-104354898 #ygrps-yiv-104354898yiv2559859138 #ygrps-yiv-104354898yiv2559859138photos div label {
color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}

#ygrps-yiv-104354898 #ygrps-yiv-104354898yiv2559859138 #ygrps-yiv-104354898yiv2559859138reco-category {
font-size:77%;}

#ygrps-yiv-104354898 #ygrps-yiv-104354898yiv2559859138 #ygrps-yiv-104354898yiv2559859138reco-desc {
font-size:77%;}

#ygrps-yiv-104354898 #ygrps-yiv-104354898yiv2559859138 .ygrps-yiv-104354898yiv2559859138replbq {
margin:4px;}

#ygrps-yiv-104354898 #ygrps-yiv-104354898yiv2559859138 #ygrps-yiv-104354898yiv2559859138ygrp-actbar div a:first-child {

#ygrps-yiv-104354898 #ygrps-yiv-104354898yiv2559859138 #ygrps-yiv-104354898yiv2559859138ygrp-mlmsg {
font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}

#ygrps-yiv-104354898 #ygrps-yiv-104354898yiv2559859138 #ygrps-yiv-104354898yiv2559859138ygrp-mlmsg table {
font-size:inherit;font:100%;}

#ygrps-yiv-104354898 #ygrps-yiv-104354898yiv2559859138 #ygrps-yiv-104354898yiv2559859138ygrp-mlmsg select, #ygrps-yiv-104354898 #ygrps-yiv-104354898yiv2559859138 input, #ygrps-yiv-104354898 #ygrps-yiv-104354898yiv2559859138 textarea {
font:99% Arial, Helvetica, clean, sans-serif;}

#ygrps-yiv-104354898 #ygrps-yiv-104354898yiv2559859138 #ygrps-yiv-104354898yiv2559859138ygrp-mlmsg pre, #ygrps-yiv-104354898 #ygrps-yiv-104354898yiv2559859138 code {
font:115% monospace;}

#ygrps-yiv-104354898 #ygrps-yiv-104354898yiv2559859138 #ygrps-yiv-104354898yiv2559859138ygrp-mlmsg * {
line-height:1.22em;}

#ygrps-yiv-104354898 #ygrps-yiv-104354898yiv2559859138 #ygrps-yiv-104354898yiv2559859138ygrp-mlmsg #ygrps-yiv-104354898yiv2559859138logo {

#ygrps-yiv-104354898 #ygrps-yiv-104354898yiv2559859138 #ygrps-yiv-104354898yiv2559859138ygrp-msg p a {
font-family:Verdana;}

#ygrps-yiv-104354898 #ygrps-yiv-104354898yiv2559859138 #ygrps-yiv-104354898yiv2559859138ygrp-msg p#ygrps-yiv-104354898yiv2559859138attach-count span {
color:#1E66AE;font-weight:700;}

color:#ff7900;font-weight:700;}

#ygrps-yiv-104354898 #ygrps-yiv-104354898yiv2559859138 #ygrps-yiv-104354898yiv2559859138ygrp-reco {

#ygrps-yiv-104354898 #ygrps-yiv-104354898yiv2559859138 #ygrps-yiv-104354898yiv2559859138ygrp-sponsor #ygrps-yiv-104354898yiv2559859138ov li a {
font-size:130%;text-decoration:none;}

#ygrps-yiv-104354898 #ygrps-yiv-104354898yiv2559859138 #ygrps-yiv-104354898yiv2559859138ygrp-sponsor #ygrps-yiv-104354898yiv2559859138ov li {

#ygrps-yiv-104354898 #ygrps-yiv-104354898yiv2559859138 #ygrps-yiv-104354898yiv2559859138ygrp-sponsor #ygrps-yiv-104354898yiv2559859138ov ul {

#ygrps-yiv-104354898 #ygrps-yiv-104354898yiv2559859138 #ygrps-yiv-104354898yiv2559859138ygrp-text {
font-family:Georgia;}

#ygrps-yiv-104354898 #ygrps-yiv-104354898yiv2559859138 #ygrps-yiv-104354898yiv2559859138ygrp-text p {
margin:0 0 1em 0;}

#ygrps-yiv-104354898 #ygrps-yiv-104354898yiv2559859138 #ygrps-yiv-104354898yiv2559859138ygrp-text tt {
font-size:120%;}

#ygrps-yiv-104354898 #ygrps-yiv-104354898yiv2559859138 #ygrps-yiv-104354898yiv2559859138ygrp-vital ul li:last-child {
border-right:none !important;}
#ygrps-yiv-104354898

Examples I use:

Current Year

TOTAL(IF Year(Today) = Year(InvcDtl.ShipDate) then InvcDtl.OurShipQty else 0)

Last Year

TOTAL(IF Year(Today) - 1 = Year(InvcDtl.ShipDate) then InvcDtl.OurShipQty else 0)

Patrick Winter

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com]
Sent: Thursday, June 30, 2016 13:22
To: vantage@yahoogroups.com
Subject: Re: [Vantage] Re: BAQ YTD calculation

So the following worked....

total(if Year(APInvHed.InvoiceDate) = year(today)  then APInvHed.InvoiceAmt else 0)

BUT then had to pull from the RcvDtl table and now I am getting the error,,,"There is a syntax or data type conversion error in "LY' calculated field expression"

total(if Year(Rcvdtl.ReceiptDate) = year(today)  then Rcvdtl.ourunitcost else 0)

On Friday, May 27, 2016 10:39 AM, "Kimberley White faepigrl@... [vantage]" <vantage@yahoogroups.com> wrote:

Worked...i have a big BUT question.

If I want a dashboard to have a date range it won't work with this calculation code correct?  I know how to change it for work for the YTD but the LY would not show correctly with that change.

Thoughts??

Thanks kim

This e-mail and any attachments may contain confidential and privileged information. If you are not the intended and/or named recipient or recipients, please notify the sender immediately by return e-mail, delete this e-mail and destroy any copies. Any dissemination or use of this information by a person other than the intended recipient or recipients is unauthorized and may be illegal. Any views or opinions expressed in this email are those of the author and do not necessarily represent those of the Specialty Screw Corporation. Warning: Although precautions have been taken to make sure no viruses are present in this email, Specialty Screw Corporation cannot accept responsibility for any loss or damage that may arise from the use of this email or attachments.
This e-mail and any attachments may contain confidential and privileged information. If you are not the intended and/or named recipient or recipients, please notify the sender immediately by return e-mail, delete this e-mail and destroy any copies. Any dissemination or use of this information by a person other than the intended recipient or recipients is unauthorized and may be illegal. Any views or opinions expressed in this email are those of the author and do not necessarily represent those of the Specialty Screw Corporation. Warning: Although precautions have been taken to make sure no viruses are present in this email, Specialty Screw Corporation cannot accept responsibility for any loss or damage that may arise from the use of this email or attachments.   ­­
Same Error.... :(

On Thursday, June 30, 2016 2:44 PM, "'Winter, Patrick' pjw@... [vantage]" <vantage@yahoogroups.com> wrote:

``````<div id="ygrps-yiv-202749548yiv5037126690ygrp-text">

<div>
``````
Examples I use:

Current Year
TOTAL(IF Year(Today) = Year(InvcDtl.ShipDate) then InvcDtl.OurShipQty else 0)
Last Year
TOTAL(IF Year(Today) - 1 = Year(InvcDtl.ShipDate) then InvcDtl.OurShipQty else 0)

Patrick Winter

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com]
Sent: Thursday, June 30, 2016 13:22
To: vantage@yahoogroups.com
Subject: Re: [Vantage] Re: BAQ YTD calculation

So the following worked....

total(if Year(APInvHed.InvoiceDate) = year(today)  then APInvHed.InvoiceAmt else 0)

BUT then had to pull from the RcvDtl table and now I am getting the error,,,"There is a syntax or data type conversion error in "LY' calculated field expression"

total(if Year(Rcvdtl.ReceiptDate) = year(today)  then Rcvdtl.ourunitcost else 0)

On Friday, May 27, 2016 10:39 AM, "Kimberley White faepigrl@... [vantage]" <vantage@yahoogroups.com> wrote:

Worked...i have a big BUT question.

If I want a dashboard to have a date range it won't work with this calculation code correct?  I know how to change it for work for the YTD but the LY would not show correctly with that change.

Thoughts??

Thanks kim

This e-mail and any attachments may contain confidential and privileged information. If you are not the intended and/or named recipient or recipients, please notify the sender immediately by return e-mail, delete this e-mail and destroy any copies. Any dissemination or use of this information by a person other than the intended recipient or recipients is unauthorized and may be illegal. Any views or opinions expressed in this email are those of the author and do not necessarily represent those of the Specialty Screw Corporation. Warning: Although precautions have been taken to make sure no viruses are present in this email, Specialty Screw Corporation cannot accept responsibility for any loss or damage that may arise from the use of this email or attachments.
This e-mail and any attachments may contain confidential and privileged information. If you are not the intended and/or named recipient or recipients, please notify the sender immediately by return e-mail, delete this e-mail and destroy any copies. Any dissemination or use of this information by a person other than the intended recipient or recipients is unauthorized and may be illegal. Any views or opinions expressed in this email are those of the author and do not necessarily represent those of the Specialty Screw Corporation. Warning: Although precautions have been taken to make sure no viruses are present in this email, Specialty Screw Corporation cannot accept responsibility for any loss or damage that may arise from the use of this email or attachments.   Â­Â­
``````</div>

<div class="ygrps-yiv-202749548yiv5037126690yqt6717126384" id="ygrps-yiv-202749548yiv5037126690yqt42929"><div style="color:#fff;height:0;"></div></div>
``````
#ygrps-yiv-202749548 #ygrps-yiv-202749548yiv5037126690 #ygrps-yiv-202749548yiv5037126690 -- #ygrps-yiv-202749548yiv5037126690ygrp-mkp { border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}

#ygrps-yiv-202749548 #ygrps-yiv-202749548yiv5037126690 #ygrps-yiv-202749548yiv5037126690ygrp-mkp hr {
border:1px solid #d8d8d8;}

#ygrps-yiv-202749548 #ygrps-yiv-202749548yiv5037126690 #ygrps-yiv-202749548yiv5037126690ygrp-mkp #ygrps-yiv-202749548yiv5037126690hd {
color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}

margin-bottom:10px;}

#ygrps-yiv-202749548 #ygrps-yiv-202749548yiv5037126690 #ygrps-yiv-202749548yiv5037126690ygrp-mkp .ygrps-yiv-202749548yiv5037126690ad p {
margin:0;}

#ygrps-yiv-202749548 #ygrps-yiv-202749548yiv5037126690 #ygrps-yiv-202749548yiv5037126690ygrp-mkp .ygrps-yiv-202749548yiv5037126690ad a {
color:#0000ff;text-decoration:none;}
font-family:Arial;}

#ygrps-yiv-202749548 #ygrps-yiv-202749548yiv5037126690 #ygrps-yiv-202749548yiv5037126690ygrp-sponsor #ygrps-yiv-202749548yiv5037126690ygrp-lc #ygrps-yiv-202749548yiv5037126690hd {
margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}

#ygrps-yiv-202749548 #ygrps-yiv-202749548yiv5037126690 #ygrps-yiv-202749548yiv5037126690actions {

#ygrps-yiv-202749548 #ygrps-yiv-202749548yiv5037126690 #ygrps-yiv-202749548yiv5037126690activity {

#ygrps-yiv-202749548 #ygrps-yiv-202749548yiv5037126690 #ygrps-yiv-202749548yiv5037126690activity span {
font-weight:700;}

#ygrps-yiv-202749548 #ygrps-yiv-202749548yiv5037126690 #ygrps-yiv-202749548yiv5037126690activity span:first-child {
text-transform:uppercase;}

#ygrps-yiv-202749548 #ygrps-yiv-202749548yiv5037126690 #ygrps-yiv-202749548yiv5037126690activity span a {
color:#5085b6;text-decoration:none;}

#ygrps-yiv-202749548 #ygrps-yiv-202749548yiv5037126690 #ygrps-yiv-202749548yiv5037126690activity span span {
color:#ff7900;}

#ygrps-yiv-202749548 #ygrps-yiv-202749548yiv5037126690 #ygrps-yiv-202749548yiv5037126690activity span .ygrps-yiv-202749548yiv5037126690underline {
text-decoration:underline;}

#ygrps-yiv-202749548 #ygrps-yiv-202749548yiv5037126690 .ygrps-yiv-202749548yiv5037126690attach {

#ygrps-yiv-202749548 #ygrps-yiv-202749548yiv5037126690 .ygrps-yiv-202749548yiv5037126690attach div a {
text-decoration:none;}

#ygrps-yiv-202749548 #ygrps-yiv-202749548yiv5037126690 .ygrps-yiv-202749548yiv5037126690attach img {

#ygrps-yiv-202749548 #ygrps-yiv-202749548yiv5037126690 .ygrps-yiv-202749548yiv5037126690attach label {
display:block;margin-bottom:5px;}

#ygrps-yiv-202749548 #ygrps-yiv-202749548yiv5037126690 .ygrps-yiv-202749548yiv5037126690attach label a {
text-decoration:none;}

#ygrps-yiv-202749548 #ygrps-yiv-202749548yiv5037126690 blockquote {
margin:0 0 0 4px;}

#ygrps-yiv-202749548 #ygrps-yiv-202749548yiv5037126690 .ygrps-yiv-202749548yiv5037126690bold {
font-family:Arial;font-size:13px;font-weight:700;}

#ygrps-yiv-202749548 #ygrps-yiv-202749548yiv5037126690 .ygrps-yiv-202749548yiv5037126690bold a {
text-decoration:none;}

#ygrps-yiv-202749548 #ygrps-yiv-202749548yiv5037126690 dd.ygrps-yiv-202749548yiv5037126690last p a {
font-family:Verdana;font-weight:700;}

#ygrps-yiv-202749548 #ygrps-yiv-202749548yiv5037126690 dd.ygrps-yiv-202749548yiv5037126690last p span {
margin-right:10px;font-family:Verdana;font-weight:700;}

#ygrps-yiv-202749548 #ygrps-yiv-202749548yiv5037126690 dd.ygrps-yiv-202749548yiv5037126690last p span.ygrps-yiv-202749548yiv5037126690yshortcuts {
margin-right:0;}

#ygrps-yiv-202749548 #ygrps-yiv-202749548yiv5037126690 div.ygrps-yiv-202749548yiv5037126690attach-table div div a {
text-decoration:none;}

#ygrps-yiv-202749548 #ygrps-yiv-202749548yiv5037126690 div.ygrps-yiv-202749548yiv5037126690attach-table {
width:400px;}

#ygrps-yiv-202749548 #ygrps-yiv-202749548yiv5037126690 div.ygrps-yiv-202749548yiv5037126690file-title a, #ygrps-yiv-202749548 #ygrps-yiv-202749548yiv5037126690 div.ygrps-yiv-202749548yiv5037126690file-title a:active, #ygrps-yiv-202749548 #ygrps-yiv-202749548yiv5037126690 div.ygrps-yiv-202749548yiv5037126690file-title a:hover, #ygrps-yiv-202749548 #ygrps-yiv-202749548yiv5037126690 div.ygrps-yiv-202749548yiv5037126690file-title a:visited {
text-decoration:none;}

#ygrps-yiv-202749548 #ygrps-yiv-202749548yiv5037126690 div.ygrps-yiv-202749548yiv5037126690photo-title a, #ygrps-yiv-202749548 #ygrps-yiv-202749548yiv5037126690 div.ygrps-yiv-202749548yiv5037126690photo-title a:active, #ygrps-yiv-202749548 #ygrps-yiv-202749548yiv5037126690 div.ygrps-yiv-202749548yiv5037126690photo-title a:hover, #ygrps-yiv-202749548 #ygrps-yiv-202749548yiv5037126690 div.ygrps-yiv-202749548yiv5037126690photo-title a:visited {
text-decoration:none;}

#ygrps-yiv-202749548 #ygrps-yiv-202749548yiv5037126690 div#ygrps-yiv-202749548yiv5037126690ygrp-mlmsg #ygrps-yiv-202749548yiv5037126690ygrp-msg p a span.ygrps-yiv-202749548yiv5037126690yshortcuts {
font-family:Verdana;font-size:10px;font-weight:normal;}

#ygrps-yiv-202749548 #ygrps-yiv-202749548yiv5037126690 .ygrps-yiv-202749548yiv5037126690green {
color:#628c2a;}

#ygrps-yiv-202749548 #ygrps-yiv-202749548yiv5037126690 .ygrps-yiv-202749548yiv5037126690MsoNormal {
margin:0 0 0 0;}

#ygrps-yiv-202749548 #ygrps-yiv-202749548yiv5037126690 o {
font-size:0;}

#ygrps-yiv-202749548 #ygrps-yiv-202749548yiv5037126690 #ygrps-yiv-202749548yiv5037126690photos div {
float:left;width:72px;}

#ygrps-yiv-202749548 #ygrps-yiv-202749548yiv5037126690 #ygrps-yiv-202749548yiv5037126690photos div div {
border:1px solid #666666;height:62px;overflow:hidden;width:62px;}

#ygrps-yiv-202749548 #ygrps-yiv-202749548yiv5037126690 #ygrps-yiv-202749548yiv5037126690photos div label {
color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}

#ygrps-yiv-202749548 #ygrps-yiv-202749548yiv5037126690 #ygrps-yiv-202749548yiv5037126690reco-category {
font-size:77%;}

#ygrps-yiv-202749548 #ygrps-yiv-202749548yiv5037126690 #ygrps-yiv-202749548yiv5037126690reco-desc {
font-size:77%;}

#ygrps-yiv-202749548 #ygrps-yiv-202749548yiv5037126690 .ygrps-yiv-202749548yiv5037126690replbq {
margin:4px;}

#ygrps-yiv-202749548 #ygrps-yiv-202749548yiv5037126690 #ygrps-yiv-202749548yiv5037126690ygrp-actbar div a:first-child {

#ygrps-yiv-202749548 #ygrps-yiv-202749548yiv5037126690 #ygrps-yiv-202749548yiv5037126690ygrp-mlmsg {
font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}

#ygrps-yiv-202749548 #ygrps-yiv-202749548yiv5037126690 #ygrps-yiv-202749548yiv5037126690ygrp-mlmsg table {
font-size:inherit;font:100%;}

#ygrps-yiv-202749548 #ygrps-yiv-202749548yiv5037126690 #ygrps-yiv-202749548yiv5037126690ygrp-mlmsg select, #ygrps-yiv-202749548 #ygrps-yiv-202749548yiv5037126690 input, #ygrps-yiv-202749548 #ygrps-yiv-202749548yiv5037126690 textarea {
font:99% Arial, Helvetica, clean, sans-serif;}

#ygrps-yiv-202749548 #ygrps-yiv-202749548yiv5037126690 #ygrps-yiv-202749548yiv5037126690ygrp-mlmsg pre, #ygrps-yiv-202749548 #ygrps-yiv-202749548yiv5037126690 code {
font:115% monospace;}

#ygrps-yiv-202749548 #ygrps-yiv-202749548yiv5037126690 #ygrps-yiv-202749548yiv5037126690ygrp-mlmsg * {
line-height:1.22em;}

#ygrps-yiv-202749548 #ygrps-yiv-202749548yiv5037126690 #ygrps-yiv-202749548yiv5037126690ygrp-mlmsg #ygrps-yiv-202749548yiv5037126690logo {

#ygrps-yiv-202749548 #ygrps-yiv-202749548yiv5037126690 #ygrps-yiv-202749548yiv5037126690ygrp-msg p a {
font-family:Verdana;}

#ygrps-yiv-202749548 #ygrps-yiv-202749548yiv5037126690 #ygrps-yiv-202749548yiv5037126690ygrp-msg p#ygrps-yiv-202749548yiv5037126690attach-count span {
color:#1E66AE;font-weight:700;}

color:#ff7900;font-weight:700;}

#ygrps-yiv-202749548 #ygrps-yiv-202749548yiv5037126690 #ygrps-yiv-202749548yiv5037126690ygrp-reco {

#ygrps-yiv-202749548 #ygrps-yiv-202749548yiv5037126690 #ygrps-yiv-202749548yiv5037126690ygrp-sponsor #ygrps-yiv-202749548yiv5037126690ov li a {
font-size:130%;text-decoration:none;}

#ygrps-yiv-202749548 #ygrps-yiv-202749548yiv5037126690 #ygrps-yiv-202749548yiv5037126690ygrp-sponsor #ygrps-yiv-202749548yiv5037126690ov li {

#ygrps-yiv-202749548 #ygrps-yiv-202749548yiv5037126690 #ygrps-yiv-202749548yiv5037126690ygrp-sponsor #ygrps-yiv-202749548yiv5037126690ov ul {