topic Re: Trending through Line chart and Bar chart in QlikView

Trending through Line chart and Bar chart

Fri, 08 Nov 2013 21:28:27 GMT

I have two requirements like below:

When there is 3 Years(MonthYear) of data, how to display the trend in a line chart for previous 6 months. When I use dimension limits, the most recent month starts at zero on the X-axis and then goes behind upto 6 months (Left Pic). I need the output in picture 2.

Also in the Bar Chart, I need show the 5 largest absolute values from an expression.

eg.

A and B are two dimensions.

SUM(A-B) for each row results the following values respectively ( 5, -10, 15,- 20, -25, 30, -35, 40, 45, 50)

In the dimension limits largest results in (15, 30, 40, 45, 50) with all bars going up.

But the requirement is to show values (30, -35, 40, 45, 50) and the bar for -35 should go down.

Thank you.

Re: Trending through Line chart and Bar chart

javier_florian — Fri, 08 Nov 2013 21:37:28 GMT

For the first issue, can you try sort order by month-year?

Re: Trending through Line chart and Bar chart

Fri, 08 Nov 2013 21:41:54 GMT

I did. But the options in the dimension limits are, first, largest and smallest.

When I do first 6, then sort in the ascending order of the monthyear, I get the 3 yr old data.

When I do first 6, then sort in the descending order, the monthyear start from current and goes back to 6 month. (as in the pic)

Re: Trending through Line chart and Bar chart

javier_florian — Fri, 08 Nov 2013 21:59:43 GMT

Nop, don't see by now the dimension limits... try with sort order:

Re: Trending through Line chart and Bar chart

Fri, 08 Nov 2013 22:24:48 GMT

but this is how I get..

Re: Trending through Line chart and Bar chart

Fri, 08 Nov 2013 22:36:02 GMT

can you please post sample qvw?

Re: Trending through Line chart and Bar chart

javier_florian — Fri, 08 Nov 2013 22:36:52 GMT

Do you try with set expression?

{<Month={">=$(=Num(Max(Month))-2)<=$(=Num(Max(Month)))"}>}

Re: Trending through Line chart and Bar chart

preminqlik — Sat, 09 Nov 2013 01:37:11 GMT

Hi there ,

try this

create one inline table in backend like below:

Month_Num:

LOAD * INLINE [

Month, MonthNum

Jan, 1

Feb, 2

Mar, 3

Apr, 4

May, 5

Jun, 6

Jiul, 7

Aug, 8

Sep, 9

Oct, 10

Nov, 11

Dec, 12

];

> Map this to your Calendar by month

>set dimensions limit to largest 5 and then

then sort the values by MonthNum.

Regards

Premhas

Re: Trending through Line chart and Bar chart

preminqlik — Sat, 09 Nov 2013 01:50:43 GMT

Hi try this

you can also sort by expression like this

=only({1}if(right(MonthYear,2)=01,1,if(right(MonthYear,2)=02,2,if(right(MonthYear,2)=03,3,if(right(MonthYear,2)=04,4,if(right(MonthYear,2)=05,5,if(right(MonthYear,2)=06,6,if(right(MonthYear,2)=07,7,if(right(MonthYear,2)=08,8,if(right(MonthYear,2)=09,9,if(right(MonthYear,2)=10,10,if(right(MonthYear,2)=11,11,if(right(MonthYear,2)=12,12)))))))))))))

also try this

=only({1}if(num(right(MonthYear,2)))

regards

Premhas