6 Replies Latest reply: May 10, 2012 12:23 AM by Matthew Green

Help needed with expression to show current financial year vs previous

Hi all,

I've got a graph that needs to show the current financial year vs previous, which I do using two expressions, the current year is

NUM(Avg({\$<data_measure={'Actual'},

DateFinYear={\$(=20 & (Num#(Mid(MaxString(DateFinYear),3,2))) & '-' & (Num#(Mid(MaxString(DateFinYear),6,2))))}>}data_value)/100,'###.##%')

and the previous year is

NUM(Avg({\$<data_measure={'Actual'},

DateFinYear={\$(=20 & (Num#(Mid(MaxString(DateFinYear),3,2))-1) & '-' & (Num#(Mid(MaxString(DateFinYear),6,2))-1))}>}data_value)/100,'###.##%')

DateFinYear is in the format of 2011-12

If I create two text boxes containing the expressions:

20 & (Num#(Mid(MaxString(DateFinYear),3,2))) & '-' & (Num#(Mid(MaxString(DateFinYear),6,2)))

20 & (Num#(Mid(MaxString(DateFinYear),3,2))-1) & '-' & (Num#(Mid(MaxString(DateFinYear),6,2))-1)

They display 2011-12 and 2010-11 when I choose 2011-12 from a list box.

However when I place the formula into my section access, it fails to work.  Can anyone see why this won't work?

Thanks

• Help needed with expression to show current financial year vs previous

Matt

I'm going to guess that the use of the \$ to calculate the value is causing a result that you don't see in your text value. The text value would be 2011-12, but forcing a calculation would give a result of 1999.

Have you considered using variables to make your set analysis cleaner, which would also allow you to see the exact value in a text box that you are using in the expression (e.g. DateFinYear={vCurrFinYear})?

Regards

David

• Re: Help needed with expression to show current financial year vs previous

I just tried moving the calculations to variables, but it didn't change anything.. The text boxes show the correct values, but the graph simply says 'No data to display'

Furthe confusion, another text box with the expression:

=IF(GetFieldSelections(DateFinYear)= vCurrentFinYear, 'Yes', 'No')

Returns 'Yes', when a value is selected from the list box.. So it is correctly matching the two values..

I'm stumped.

• Re: Help needed with expression to show current financial year vs previous

When using variables in set analysis, I use a format like this:

```Sum({\$ <ReportedYearNum={\$(=vMaxFinancialYearNum)} >} PaymentAmount)
```

If I have problems with strings, like financial year, I usually add another column with an integer value instead, and use that in the variable and set analysis expresion.

• Re: Help needed with expression to show current financial year vs previous

Nope, that doesn't seem to work either Michael

• Help needed with expression to show current financial year vs previous

Hi Matt G expression for current year

Sum({<Year={'\$(=Year)'}>} Sales) and for previous year Sum({<Year={'\$(=Year-1)'}>} Sales) , these two expressions will show the difference between current year selection and previous year, if you use in Bar chart.

hope this solution will solve your task. update if this work, if not plz share your application document.

Thanks

• Re: Help needed with expression to show current financial year vs previous

That helped.. I didn't have the ' ' around my expression..

Changing DateFinYear={\$(=vCurrentFinYear)} to DateFinYear={'\$(=vCurrentFinYear)'} fixed it..

Thanks..