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

[#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

[Anil_Babu_Samineni]

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

[zhadrakas]

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))

[Anil_Babu_Samineni]

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

[sunny_talwar]

What is the numerical output you are looking to get?