Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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.
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.
Nope, that doesn't seem to work either Michael
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
That helped.. I didn't have the ' ' around my expression..
Changing DateFinYear={$(=vCurrentFinYear)} to DateFinYear={'$(=vCurrentFinYear)'} fixed it..
Thanks..