topic Re: Re: Graphic Help in QlikView

Graphic Help

Sat, 01 Feb 2014 19:45:23 GMT

Hi!

I'm trying to create a graphic of trend of costs in each year, where:

1. Total costs in 2011 is my reference line;

2. Y-axis shows percentage values from -100% to +100%, and total costs in 2011 value line starts from 0%;

3. On the total costs in 2012 line should be in evidence of the difference in percentage of total costs in 2011;

4. On the total costs in 2013 line should be in evidence of the difference in percentage of total costs in 2011;

Can you maybe help me with the example attached?

Thank you for any help ou can provide in this situation.

Regards,

Andrea

Re: Graphic Help

Mon, 03 Feb 2014 18:22:35 GMT

Can anybody help me pls?

Re: Graphic Help

Gysbert_Wassenaar — Mon, 03 Feb 2014 19:03:07 GMT

See attached qvw. Is that what you're looking for?

Re: Graphic Help

jerem1234 — Mon, 03 Feb 2014 19:06:31 GMT

Please find attached to see if its what you are looking for. What I am guessing you want is like ((Values of 2012) - (Values of 2011))/(Values of 2011).

I created two charts, one with the y-axis untouched and the other limited to -100% to 100%. But I do have to point out that you may get values larger than 100% and smaller than -100% since its about how much larger or smaller the year is compared to 2011 (Could be 200% more than 2011 for example).

Hope this helps!

Re: Graphic Help

Mon, 03 Feb 2014 21:05:34 GMT

Thank you so much for your help!!

Re: Graphic Help

Mon, 03 Feb 2014 21:07:26 GMT

Thank you so much for your help!

Re: Re: Graphic Help

Mon, 10 Feb 2014 19:12:16 GMT

Hi! I'm trying to create a graphic as your example, but only considering these data ranges:

1) 2010/2011: date from 11/2010 to 03/2011. Is my reference line, and starts from 0%;

2) 2011/2012: from 11/2011 to 03/2012. Should be in evidence of the difference in percentage of total costs 2010/2011;

3) 2012/2013: from 11/2012 to 03/2013.Should be in evidence of the difference in percentage of total costs 2010/2011;

The result should be something as this graphic:

Could you help me to write the expression pls?

I tried to use something like this:

(sum({<year={2011}, month= {’11’,’12’}>+<{<year={2012}, month= {’1’,’2’,’3’}>}cost)

-Sum({<year={2010}, month= {’11’,’12’}>+<{<year={2011}, month= {’1’,’2’,’3’}>} cost))

/Sum({<year={2011}, month= {’11’,’12’}>+<{<year={2012}, month= {’1’,’2’,’3’}>}cost)

But I don't know how to insert all range data options.

I really appreciate any help you can provide.

Regards,

Andrea

Re: Re: Re: Graphic Help

jerem1234 — Mon, 10 Feb 2014 21:37:28 GMT

You would want to create a new field in your script like:

(year(addmonths(makedate(year,month),2))-1)& '/' & year(addmonths(makedate(year,month),2)) as YY

Then use that as only dimension with an expression like:

(sum({<month= {1,2,3,11,12}>}cost)

-Sum({<YY={'2010/2011'},month= {1,2,3,11,12}>}total cost))

/Sum({<YY={'2010/2011'},month= {1,2,3,11,12}>}total cost)

Please find attached for an example.

Hope this helps!

Re: Re: Re: Re: Graphic Help

Mon, 10 Feb 2014 22:09:47 GMT

Hi Jerem! Thank you so much for help!!

I have some problems to create a new field. If I use an expression like this as Dimension:

If(([Year] = 2010 and ([Month]= 11 or [Month]= 12)) or ([Year] = 2011 and ([Month= 1 or [Month]= 2 or [Month]= 3)),'2010/2011',
If (([Year] = 2011 and ([Month]= 11 or [Month]= 12)) or ([Year] = 2012 and ([Month]= 1 or [Month]= 2 or [Month]= 3)),'2011/2012',
If (([Year] = 2012 and ([Month]= 11 or [Month= 12)) or ([Year] = 2013 and ([Month]= 1 or [Month]= 2 or [Month]= 3)),'2012/2013')))

Do you think there is another way to write the expression?

I will be grateful if you can send me this information.

Regards,

Andrea

Re: Graphic Help

jerem1234 — Mon, 10 Feb 2014 22:16:15 GMT

What's the problem with making extra field? If the problem is adding to script, then maybe adding the calculated dimension with same thing would do it?

=(year(addmonths(makedate(year,month),2))-1)& '/' & year(addmonths(makedate(year,month),2))

as dimension and use the expression I used above.

What problems are you having creating the dimension?

Re: Graphic Help

Tue, 11 Feb 2014 19:58:30 GMT

Sorry, I have been too busy today to answer your message in time. I'd like to thank you very much for your patience and helpfulness, especially because I'm not so skilled about the matter!

I'm trying to create this graphic following your second option. Maybe it's a silly question...

If I add the calculated dimensions, the program dont recognize YY field. How should I do to set YY, without create a new field?

Is it possibile to bound YY data only to 2010/2011, 2011/2012 and 2012/2013. I dont want another value, even if there are others years in my data loaded.

Thank you for any help you can provide in this situation.

Regards,

Andrea

Re: Re: Graphic Help

jerem1234 — Tue, 11 Feb 2014 20:25:40 GMT

That is not a silly question, because that is entirely my mistake, I forgot to change the expression .

Used this as my dimension:

(year(addmonths(makedate(year,month),2))-1)& '/' & year(addmonths(makedate(year,month),2))

Then for expression:

(sum({<month= {1,2,3,11,12}>}cost)-

top(sum({<month= {1,2,3,11,12}>}cost)))

/top(sum({<month= {1,2,3,11,12}>}cost))

If you just want the first 3, then use dimension limits -> Show only -> First -> 3 values and un-check Show Others.

Of course this depends on if the first 3 values are the ones you want. If not, you may have to add more code to get there.

Please find attached.

Hope this helps!

Re: Graphic Help

Tue, 11 Feb 2014 22:10:13 GMT

It was very kind of you to help me!

The expression creates these dimensions: 2009/2010, 2010/2011, 2011/2012, 2012/2013 and 2013/2014.

Using limit dimension First 3, I can't select the range from 2010/2011 to 2012/2013.

Could you suggest a solution, please?

I will be grateful if you can send me this information.

Andrea

Re: Graphic Help

jerem1234 — Wed, 12 Feb 2014 14:02:52 GMT

Change your dimension to:

=if(match((year(addmonths(makedate(year,month),2))-1)& '/' & year(addmonths(makedate(year,month),2)), '2010/2011', '2011/2012', '2012/2013'),

(year(addmonths(makedate(year,month),2))-1)& '/' & year(addmonths(makedate(year,month),2)))

This is a rather cumbersome calculated dimension, so I am not sure how performance will be if you have a large data set. But this should get you what you want.

Hope this helps!

Re: Graphic Help

Wed, 12 Feb 2014 19:48:17 GMT

I don't know how to thank you!!