Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
Customers, Partners & Luminaries only: You're invited to a Data Analytics Roadmap session. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Value Loop and Input Boxes - Create Bar Chart

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

1 Solution

Accepted Solutions
Highlighted
Luminary
Luminary

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

View solution in original post

9 Replies
Highlighted
Not applicable

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

Highlighted
Luminary
Luminary

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

View solution in original post

Highlighted
Luminary
Luminary

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

Highlighted
Not applicable

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

Highlighted
Master
Master

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

Highlighted
Specialist
Specialist

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

Highlighted
Creator
Creator

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

Highlighted
Luminary
Luminary

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

Cheers,

DV

www.QlikShare.com

Highlighted
Not applicable

Hi Deepak,

See if you can help on this question

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