Hello,

I am using the downloadable Container "Sales Analysis SX3 1-0 (C4-0-2)" on Sage Intelligence for X3 v7.42.2.  This is a Join container type.

Want to create a "last year" metric.

Current Expression:

SALES 

CASE WHEN INV.INVTYP_0 = 2 THEN -1
ELSE 1 END *
(INVD.AMTNOTLIN_0 * INV.RATMLT_0 )

Additional desired expression:  (Not Working)

SALES LY

CASE WHEN INV.INVTYP_0 = 2 THEN -1
ELSE 1 END *
(INVD.AMTNOTLIN_0 * INV.RATMLT_0 ) DATEADD(YY,-1,INVD.AMTNOTLIN_0)

SQL Syntax error.

Per another thread I have set the container to 'execute with Universal Query Engine'.  Is this the only way to use DATEADD?  As this gives a host of other errors.

Any better suggestions to achieve a LAST YEAR transformed Expression in the Container?

Thanks for any help!

Views: 220

Reply to This

Replies to This Discussion

Hi Erik.

Can you please be a bit more clear on the purpose of the last year metric.  I'm not sure how you want this to tie in with the turnover field.

Hello Peter,

Here is a usage example of this metric.  In a Pivot Table I have the Year (2017) as a filter, in Values I then have the field Turnover ($'s would be for 2017) and Turnover Last Year ($'s would be for 2017 less 1 or 2016).

This setup enables a lot of analysis that just putting Year in the Columns of a report does not achieve.

Ultimately, the syntax and usage would be extended to Fields beyond Turnover, for example Margin, Extended Cost, etc.

Thanks for you assistance,

Erik

Try this:

  • Put two new fields in the Data sheet (sheet 1) like "TurnoverCurrentYear", and "TurnoverLastYear".
  • Calculate (in Excel) something like in the currentyear column: (if G2= 2017, F2, "")  where G2 = Fiscal Year, F2 = Turnover. In the previous year column you would have (if G2= 2016, F2, ""). You can probably use an Excel formula if you want to make it automatically figure out what the "Current" year is vs "Last" year, rather than hard code the 2017 and 2016.
  • Use the Intelligence Add-in to calculate the results of the formula for all of the rows on sheet2.
  • Use the Currentyear and last year columns in your pivot table. 
  • You could do the same thing for Margin and cost fields. 

I think that would be easier than editing the SQL expression in the container. 

Just a thought.

Hi Erik.

You won't be able to include prior year turnover amounts in a separate column.  The reason is that the report returns records on a transaction basis so to try and line up amounts for prior years with current year transactions isn't possible.

I'm not sure if this is what you were referring to when you said "this setup enables a lot of analysis that just putting Year in the Columns of a report doesn't achieve" above, but I thought you could do the following using pivot tables.  Take a look at the attached workbook where I've modified the Top Customers by Month pivot as well as created another view in the Demo 2 sheet.  This shows your current and prior years side by side.  I've removed the document number field as it is unique and so values wouldn't be shown sided by side, hence transactions are rolled up into months.

If this is what you're after then what you can also look at doing is adding a distinct year field to your container, i.e. to give just the year of a transaction, and could then add a parameter and pass through variable to only bring back your current and prior years data.

If I'm wrong about this then I suggest drawing up a spreadsheet of what you would like the report to look like and I can try help form there. 

Attachments:

RSS

The Sage Intelligence Blog

Like Sage Intelligence?

Follow @SageSupport

© 2019   Created by Sage Alchemex.   Powered by

Badges  |  Report an Issue  |  Terms of Service