4 Replies Latest reply: Jul 19, 2017 8:20 AM by Sunny Talwar

Expression assitance - (Sum sales if financial year - 1)

Good afternoon,

I have a bit of a query where I am struggling to get the correct information.  We are trying to calculate the previous year to date sales, below the table shows what the value should be for each year:

What I want to try and do is have the value for this year, but also what the sales were previously in a separate column.  With the formula I'm working ( =Sum({<FINYEAR = {'\$(=max(FINYEAR)-1)'}, TYPE = {'EBUS'}, FINMONTH = {'<=\$(vMonthName)'}>}SALES) ) with I'm getting this:

I've tried breaking down the formula, a SUM(IF( statement isn't working.....can anyone suggest something else? (FYI the PYTD formula is =Sum({<FINYEAR = {'\$(=max(FINYEAR)-1)'}>}SALES) )

TIA

• Re: Expression assitance - (Sum sales if financial year - 1)

Looks like you are getting last year data? What was the wrong

• Re: Expression assitance - (Sum sales if financial year - 1)

And, Adding you are doing some filters with in expression. That is why you are getting correct values. Why you removed the filters.

• Re: Expression assitance - (Sum sales if financial year - 1)

you could remove Year in Dimensions and try this expressions.

You Need to create 3 variables:

vMaxYear = max(FINYEAR)

vPrevYear = max(FINYEAR)-1

vPrevPrevYear = max(FINYEAR)-2

Expressions:

Sum(if(FINYEAR =\$(vCurrentYear), SALES)

Sum(if(FINYEAR =\$(vPrevYear ), SALES)

Sum(if(FINYEAR =\$(vPrevPrevYear ), SALES)

• Re: Expression assitance - (Sum sales if financial year - 1)

What is the numerical output you are looking to get?