topic Re: Value Loop and Input Boxes - Create Bar Chart in QlikView

Value Loop and Input Boxes - Create Bar Chart

Fri, 25 May 2012 08:39:13 GMT

Hi Guys,

I am trying to build a qlikview model where there are a few input boxes that the EU can use and will be called later to populate the report. What I wanted to do was chart the values this person has selected for each.

So what is happening is this. vYear1 = 100, vYear2 = 200 ...vYearN = N..etc. I now want to create a bar chart with Year as the axis.

In the model is a variable called Financial Year, lets say its 2012, and a variable called modelling years, lets say 5. I have used the function Valueloop to create the dimension

=ValueLoop(vFinancialYear,vFinancialYear + vModellingYears).... Gives me 2012, 20132, 2014....n etc as my axis. Now how do i call the values from the input boxes and correctly allocate them to the year on the axis.

I've attached a model that shows what im trying to achieve

Cheers,

Byron

Value Loop and Input Boxes - Create Bar Chart

Fri, 25 May 2012 09:48:18 GMT

Guys, Im hoping there is a much more elegent way of doing it then using this as the expression

if(Valueloop(vFinancialYear,vFinancialYear+(vModellingYears-1))=2012,vYear1,

if(Valueloop(vFinancialYear,vFinancialYear+(vModellingYears-1))=2013,vYear2,

if(Valueloop(vFinancialYear,vFinancialYear+(vModellingYears-1))=2014,vYear3,

if(Valueloop(vFinancialYear,vFinancialYear+(vModellingYears-1))=2015,vYear4,

if(Valueloop(vFinancialYear,vFinancialYear+(vModellingYears-1))=2016,vYear5,

if(Valueloop(vFinancialYear,vFinancialYear+(vModellingYears-1))=2017,vYear6,

if(Valueloop(vFinancialYear,vFinancialYear+(vModellingYears-1))=2018,vYear7,

if(Valueloop(vFinancialYear,vFinancialYear+(vModellingYears-1))=2019,vYear8,

if(Valueloop(vFinancialYear,vFinancialYear+(vModellingYears-1))=2020,vYear9,

if(Valueloop(vFinancialYear,vFinancialYear+(vModellingYears-1))=2021,vYear10))))))))))

Re: Value Loop and Input Boxes - Create Bar Chart

IAMDV — Fri, 25 May 2012 10:04:49 GMT

Hi Byron,

I think hard coded IF statements won't work in your case. Because whenever EU changes the Years which are not within the IF statement range then you need to change the expression and there is maintenance cost or you need to write massive expression to check the conditions.

I would solve this problem by using Pick and Match functions. Something like this...

Pick( Match(Valueloop(vFinancialYear,vFinancialYear+(vModellingYears-1)), $(=vMyYears)), $(=vMyText))

And I have created two variable vMyText and vMyYears. There is one issue with this approach i.e. when EU assigns 10 as Modelling Years the sort order in my expression is incorrect however this works until you select 9 as Modelling Years. I'll try to solve this one for you. Meanwhile you can play around with this. I'm attaching the sample app.

Good luck!

Cheers,

www.QlikShare.com

Re: Value Loop and Input Boxes - Create Bar Chart

IAMDV — Fri, 25 May 2012 10:25:53 GMT

Byron - I think it works fine and please ignore my previous message about not working. It works for all the years and for some reason I thought there is an issue. My mistake!

I hope this is what you need.

Cheers,

www.QlikShare.com

Value Loop and Input Boxes - Create Bar Chart

Fri, 25 May 2012 10:56:23 GMT

Absolutely fantastic DV! This was 'eleqantly' done

I really like what you did with the variables vMyText and vMyYears. Thank you very much for taking the time to get this to work for me.

Cheers,

Byron

Re: Value Loop and Input Boxes - Create Bar Chart

paulyeo11 — Tue, 02 Jul 2013 09:50:47 GMT

Hi Dv

i saw your blog comment link , i hope you can share a bit more on this example , as i am unable to figure out how it work. it is vey power ful.

paul

Re: Value Loop and Input Boxes - Create Bar Chart

rajni_batra — Mon, 14 Oct 2013 07:18:17 GMT

Hello Deepak,

I have attached a dummy file to explain my problem, In sheet one there is a contract with given Valid from valid to date

and in sheet 2 there is year on year variance for same contracts,

In sheet 3 i have mentioned the output desired.

i.e. if two contracts have diff valid from date but user wants them to be in a single column for there respective 1st contractual , 2nd contractual year and so on .can this be done through valueloop ??

Any help would be appreciated!!! thanks

Regards,

Rajni

Re: Value Loop and Input Boxes - Create Bar Chart

Anonymous — Fri, 04 Jul 2014 11:52:29 GMT

Hi,

Thanks for sharing qvw file (Sales Planning Model v5.01.qvw).

It helped me to understand the concepts of not only valueloop() but also pick(),match() and concat() functions.

Thanks once again

Regards,

Geeta

Re: Re: Value Loop and Input Boxes - Create Bar Chart

IAMDV — Sun, 06 Jul 2014 09:05:16 GMT

That's great. I'm glad it was helpful.

Cheers,

www.QlikShare.com

Re: Value Loop and Input Boxes - Create Bar Chart

Mon, 28 Nov 2016 12:34:41 GMT

Hi Deepak,

See if you can help on this question

How to make pie chart of sum of metrices as dimensions ?