Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

6 Replies
Not applicable
Author

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

Not applicable
Author

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.

Not applicable
Author

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. 

Not applicable
Author

Nope, that doesn't seem to work either Michael

qlikviewajeet
Contributor III
Contributor III

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

Not applicable
Author

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

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

Thanks..