http://52.173.207.40/wiki/index.php?action=history&feed=atom&title=Keystone_FXKeystone FX - Revision history2026-04-24T21:20:37ZRevision history for this page on the wikiMediaWiki 1.34.0http://52.173.207.40/wiki/index.php?title=Keystone_FX&diff=341&oldid=prevUser: 1 revision imported2020-01-08T13:00:05Z<p>1 revision imported</p>
<table class="diff diff-contentalign-left" data-mw="interface">
<tr class="diff-title" lang="en">
<td colspan="1" style="background-color: #fff; color: #222; text-align: center;">← Older revision</td>
<td colspan="1" style="background-color: #fff; color: #222; text-align: center;">Revision as of 13:00, 8 January 2020</td>
</tr><tr><td colspan="2" class="diff-notice" lang="en"><div class="mw-diff-empty">(No difference)</div>
</td></tr></table>Userhttp://52.173.207.40/wiki/index.php?title=Keystone_FX&diff=340&oldid=preven>Chanson: /* Date Presentation */2019-05-08T17:46:27Z<p><span dir="auto"><span class="autocomment">Date Presentation</span></span></p>
<p><b>New page</b></p><div><br />
<br />
<br />
<br />
<br />
==Formulas==<br />
<br />
===Date Presentation===<br />
:'''This Year''':<code>=ThisYr</code> "2019"<br />
:'''This Month''':<code>=ThisPer</code> "6"<br />
:'''This Month Name''':<code>=VLOOKUP(ThisPer,TblPDQty[#Data],3,FALSE)</code> "June"<br />
:'''This Month and Year''': <code>=VLOOKUP(ThisPer,TblPDQty[#Data],3,FALSE)&" "&ThisYr</code> Example: "June 2019"<br />
<br />
===G/L Values===<br />
Keystone FX returns G/L values using the SUMIFS formula. SUMIFS sums up a specified value with as many filters as you need. For G/L we extract data from the table "TblGLPDSUM" and filter by both periods and accounts.<br />
<br />
<br />
Common Formulas<br />
:'''This Per Amt for a Core Account''': <code>=SUMIFS(TblGLPDSUM[PER_AMT],TblGLPDSUM[S1],"="&$A10,TblGLPDSUM[PERIOD_YEAR],"="&ThisYr,TblGLPDSUM[PERIOD_NO],"="&ThisPer)</code><br />
:'''YTD Amt for a Core Account''': <code>=SUMIFS(TblGLPDSUM[PER_AMT],TblGLPDSUM[S1],"="&$A10,TblGLPDSUM[PERIOD_YEAR],"="&ThisYr,TblGLPDSUM[PERIOD_NO],"<="&ThisPer)</code> (Simply change the "=" to "<=" for the period filter)<br />
:'''This Per Amt for a Group''': <code>=SUMIFS(TblGLPDSUM[PER_AMT],TblGLPDSUM[S1],"="&$A10,TblGLPDSUM[PERIOD_YEAR],"="&ThisYr,TblGLPDSUM[PERIOD_NO],"="&ThisPer)</code><br />
<br />
===Values===<br />
:'''Period Amount:''' <code>TblGLPDSUM[PER_AMT]</code><br />
:'''Budget Amount:''' <code>TblGLPDSUM[BUD_AMT]</code><br />
:'''Year Begin Amount:''' <code>TblGLPDSUM[YR_BEG_AMT]</code> Requires the filter (TblGLPDSUM[PERIOD_NO],"=1",TblGLPDSUM[PER_AMT],"=0") or newer (TblGLPDSUM[PERIOD_NO],"=0",TblGLPDSUM[PER_AMT],"=0")<br />
<br />
Also Available but rarely used...<br />
:'''Y-T-D Amount:''' <code>TblGLPDSUM[YTD_AMT]</code> (Instead use "PER_AMT" with a <=ThisPer filter.)<br />
:'''Y-T-D Budget:''' <code>TblGLPDSUM[YTD_BUD]</code> (Instead use "BUD_AMT" with a <=ThisPer filter.)<br />
<br />
<br />
====Account Selection====<br />
Account Selection Filters are commonly combined. For example on a plant income statement you'll likely combine a core account filter and a plant filter.<br />
<br />
These examples are based on these Assumptions:<br />
:'''S1:''' Core Account, typically segment 1<br />
:'''S2:''' Plant, typically segment 2<br />
:'''S3:''' Division. typically segment 3<br />
This can vary depending from system to system but the concept is still the same.<br />
<br />
Account Selection Filters:<br />
:'''The Core Account:''' <CODE>TblGLPDSUM[S1],"="&$A10</CODE><br />
:'''Range of Core Accounts:'''<CODE>TblGLPDSUM[S1],">="&$A10,TblGLPDSUM[S1],"<="&$B10</CODE><br />
:'''Keystone GL Group:''' <CODE>TblGLPDSUM[GRP],"="&$A10</CODE><br />
:'''Specific Plant:'''<CODE>TblGLPDSUM[S2],"="&PlantNo</CODE> (Here PlantNo is a named value, typically tied to the worksheet)<br />
:'''A Single G/L Account:''' <CODE>TblGLPDSUM[GL_ACCOUNT],"="&$A10</CODE> (Rarely Used - Normally combine Core Account and Plant Filter etc.)<br />
:'''Divison:''' <CODE>TblGLPDSUM[S3],"="&DivisionNo</CODE> (Here DivisionNo is a named value, typically tied to the worksheet)<br />
<br />
====Period Selection====<br />
Period Selection relies heavily on the named values on the setup tab:<br />
:'''ThisYr:''' The current reporting year<br />
:'''ThisPer:''' Current Period<br />
:'''LastYr:''' Last Year<br />
:'''PerCapx12:''' Normally the same as ThisPer - Limits which periods display on a 12 month report.<br />
<br />
<br />
Periods Filters:<br />
:'''Current Period:''' <code>TblGLPDSUM[PERIOD_NO],"="&ThisPer</code><br />
:'''Current Year:''' <code>TblGLPDSUM[PERIOD_YEAR],"="&ThisYr</code><br />
:'''Last Year:''' <code>TblGLPDSUM[PERIOD_YEAR],"="&LastYr</code><br />
<br />
The magic comes from combining filters...<br />
:'''Current Period Last Year:''' <code>TblGLPDSUM[PERIOD_NO],"="&ThisPer,TblGLPDSUM[PERIOD_YEAR],"="&LastYr</code><br />
:'''Year to Date This Year:''' <code>TblGLPDSUM[PERIOD_NO],"<="&ThisPer,TblGLPDSUM[PERIOD_YEAR],"="&ThisYr</code><br />
<br />
===Per Unit Sales===<br />
Similar to returning g/l data, certain sales data can be returned in with a formula. The common case for this is Cubic Yards of Concrete but FX can be customized to return other values as well.<br />
<br />
Common Formula:<br />
:'''Qty Sold This Period:''' <code>=SUMIFS(TblSLSQTY[QTY SOLD],TblSLSQTY[PERIOD_YEAR],"="&ThisYr,TblSLSQTY[PERIOD_NO],"="&ThisPer)</code><br />
:'''Qty Sold Year to Date:''' <code>=SUMIFS(TblSLSQTY[QTY SOLD],TblSLSQTY[PERIOD_YEAR],"="&ThisYr,TblSLSQTY[PERIOD_NO],"<="&ThisPer)</code></div>en>Chanson