Table to display invoice amount by a post date?

[Anonymous]

Hey there - I'm trying to create a table that reports some information but it has some requirements to it.

The first column of the table has the sales rep ID.  The second column has a month.  The third and fourth columns I'd like to perform a calculation based on the invoice amount during specific periods.

For column 3: it should be Sum(InvAmt) Where PostDate = Month AND currentyear AND sales rep ID

I'm having trouble with the syntax to come up with the correct equation to do that.  

The data is being pulled out of an SQL Server and each invoice has a sales rep id attached to it and a post date which I've loaded and created the relationship between in the data manager.

Any help would be greatly appreciated.  Thank you!

[hallquist_nate]

Since Columns 1 and two are RepID and Month respecively, you don't need to add those elements to your expression.  You really only need to add something for current year.  Since this is an expression, and not a Load Script statement, you don't use WHERE, you would use Set Analysis.  In this case, the formula would look something like this....

Sum({$<CurrentYear={1} >} InvAmt)  

The only element you really need to deal with is the CurrentYear Indicator.  Typically, I like to add that script to my Master Calendar.  I write a script that basically flags the Date as either "in the current year" (with a "1"), everything other date value is a "0".  if you name that flag "CurrentYear", then the above expression should work.

[Anonymous]

Thank you!

[Anonymous]

This worked perfectly! How do I got about finding the same information but for one year back? For example - usually it might be something along the lines of CurrentYear-1 to go back one year but is there a previous year function built in or ?

[hallquist_nate · Accepted solution]

We have a Master Calendar.  I HIGHLY recommend that you find one or build one.  They are immensely powerful.  Here is what we use for our Current Year, Last Year, Next Year and ect.

// Year flags
inyear(Date, $(vToday), 0) * -1 AS CY, //Current year
inyear(Date, $(vToday), -1) * -1 AS FPY, //First prior year
inyear(Date, $(vToday), -2) * -1 AS SPY, //Second prior year
inyear(Date, $(vToday), 1) * -1 AS NY, //Next year
inyear(Date, $(vToday), -1) * -1 AS LY, //Last year (Same as FPY)

These create values when the statement is True, the value is one, where false it is 0 (zero).  We then use these in Set analysis expressions.  The most efficient Set analysis uses flags like this, so our expression formulas are efficient and simple.  For example, for Last Year Sales, our expression looks like this...

Sum({$< LY={1}>} Sales)

For Year-to-Date, we do something similar.  We use the following Script..

If(InYearToDate(Date, $(vToday), 0)
or InYearToDate(Date, $(vToday), -1)
or InYearToDate(Date, $(vToday), -2)
or InYearToDate(Date, $(vToday), -3)
or InYearToDate(Date, $(vToday), -4)
, 'YTD') as YTD,

This create a field named "YTD".  If the KeyDate is within the YTD for any of the past 4 years, the value returned is "YTD".  This is a bit different, so let's go through a quick example.  

Let's pretend that today is October 1, 2018.  The date that is in question is Sept. 15, 2017.  If we use the script above for YTD, any line item that has a date of Sept. 15th, regardless of year, would get a YTD value associated.  We can then use that in expression to compare Last Year-to-Date vs Current Year-to-Date.  The expressions would look like this...

Current Year-to-Date:

Sum({$< CY={1}, YTD={'YTD'}>} Sales)

Last Year-to-Date:

Sum({$< LY={1}, YTD={'YTD'}>} Sales)

Hope this not only helps you out with your current challenges, but also helps you out in the future.

Nate

[Anonymous]

Thank you Nate - I appreciate it!