topic Re: Getting Last year as reference line, when i select a year in field in QlikView

Getting Last year as reference line, when i select a year in field

VenkateshYarra — Tue, 11 Feb 2020 15:09:51 GMT

Hello Everyone,

I need to show the reference line of last year archived on selected year bar.

I am using as following

Dimension = FY ( nothing but year filed )

Expression = Num((Count($(vFormulaBelowPrfMin)) / $(vFormulaTotalProjects)), '###.0%')

Now if i select FY = 2020, then bar will show the 2020 achieved %, Now i need to show the reference line on bar chart what is achieved as last year in 2019.

I am not getting the expression how to write for last year.

Please need help your guys..

Thank you

Venkatesh

Re: Getting Last year as reference line, when i select a year in field

Arthur_Fong — Wed, 12 Feb 2020 05:07:32 GMT

What is the expression for these 2 variables?

$(vFormulaBelowPrfMin)

$(vFormulaTotalProjects)

Re: Getting Last year as reference line, when i select a year in field

Brett_Bleess — Tue, 10 Mar 2020 20:02:01 GMT

Venkatesh, are you still trying to figure things out on this post, or did you get something worked out? If the latter, please consider posting what you did and then use the Accept as Solution button on it after you post it to mark the thread as complete. If you still need further help, please reply to Arthur's last post with content of the two variables.

Regards,
Brett

Re: Getting Last year as reference line, when i select a year in field

VenkateshYarra — Wed, 11 Mar 2020 06:25:46 GMT

vFormulaBelowPrfMin=

Aggr({<$(vCurrentTargetFoundation)=>} If((Sum(Aggr($(vCurrentTargetMinimum), %KEY_ISO_FyQ_SDconv_OLI)) > (Aggr($(vFormulaPRF), %KEY_ISO_FyQ_SDconv_OLI)) or IsNull(Aggr($(vCurrentTargetMinimum), %KEY_ISO_FyQ_SDconv_OLI)))
and (Aggr($(vFormulaPRF), %KEY_ISO_FyQ_SDconv_OLI)) > 0, [Opportunity Line Number], Null()), [Opportunity Line Number])

vFormulaTotalProjects=
Count(DISTINCT [Opportunity Line Number])

Below is the requirement. Please see the attached image.

Thank you

Regards

Venkatesh Yarra

Re: Getting Last year as reference line, when i select a year in field

Arthur_Fong — Wed, 11 Mar 2020 08:19:46 GMT

I couldn't help if your variables are nested with another variables, unless you can provide a sample app.

Or maybe others can help.

Re: Getting Last year as reference line, when i select a year in field

Brett_Bleess — Wed, 11 Mar 2020 11:35:42 GMT

To Arthur's point, if you are using variables inside variables, that is a very dangerous thing to do, as there is no guarantee of the calculation order of things here, so if you have done that, you should likely try replacing the variables inside the other variables with their associated expressions etc. and see how things then work, about the best I can offer in addition to what Arthur stated just to try to clarify things a bit further.

Regards,
Brett

Re: Getting Last year as reference line, when i select a year in field

VenkateshYarra — Wed, 11 Mar 2020 11:48:34 GMT

Sorry Arthur,

Thank you for your response.

I can't share the qvw file with sample data ( Due to my organization policy ). Any how it is huge data and coming from different qvd.

Any how i changed the view of the graph. Now i am not showing the last year data.

Thank you for your help.

Regards

Venkatesh