topic Re: Help needed with expression to show current financial year vs previous in QlikView

Help needed with expression to show current financial year vs previous

Thu, 10 May 2012 01:14:28 GMT

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

Thu, 10 May 2012 02:14:54 GMT

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

Thu, 10 May 2012 02:35:20 GMT

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

Thu, 10 May 2012 03:48:25 GMT

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

Thu, 10 May 2012 04:01:10 GMT

Nope, that doesn't seem to work either Michael

Help needed with expression to show current financial year vs previous

qlikviewajeet — Thu, 10 May 2012 04:12:07 GMT

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

Thu, 10 May 2012 04:23:38 GMT

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

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

Thanks..