Value Loop and Input Boxes - Create Bar Chart

[#1]

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

[#2]

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))))))))))

[IAMDV · Accepted solution]

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,

DV

www.QlikShare.com

[IAMDV]

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,

DV

www.QlikShare.com

[#5]

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

[paulyeo11]

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

[rajni_batra]

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

[Anonymous]

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

[IAMDV]

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

Cheers,

DV

www.QlikShare.com

[#10]

Hi Deepak,

See if you can help on this question

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