topic Re: Chart with Monthly Trend and Targets in QlikView

Chart with Monthly Trend and Targets

Wed, 19 Feb 2014 16:53:30 GMT

Dear all,

I'm getting crazy with this problem.

I have the following excel tables that I load from two different files:

Table 1: Events. Single events occurring in a specified month and with a dedicated description

Site	Month	Event Description
A	1	utyu
B	1	tyuty
A	1	tyuty
C	1	jhgj
D	2	hfghf
A	2	wrwer
C	2	werwe
B	2	r5435
E	3	sdfsd
…	…	…

Table 2: Cumulative targets on the number of events, per Site, per month

Site	Month	Cum Target
A	1	3
A	2	5
A	3	7
A	4	9
A	5	11
A	6	13
A	7	15
A	8	17
A	9	19
A	10	21
A	11	23
A	12	25
B	1	1
B	2	3
B	3	5
B	4	7
B	5	9
B	6	11
B	7	13
B	8	15
B	9	17
B	10	19
B	11	21
B	12	23
…	…	…

I'd like to draw a bar+line chart where I have:

the year months (1 to 12) on the X axis
the number of events per month as a bar chart, added cumulatively month by month
the sum of the cumulative targets per month as a line chart

then I could filter on the "site" in order to get specific details.

I seek your help as I can't understand how to properly set the dimension and the expressions.

I think that I should set a fixed dimension from 1 to 12 and then get the right expressions to collect and sum the results per each month on the two tables.

But currently I'm not able to get such result.

Thank you for any help.

Giuseppe

Re: Chart with Monthly Trend and Targets

Gysbert_Wassenaar — Wed, 19 Feb 2014 17:22:17 GMT

See attached qvw.

Re: Chart with Monthly Trend and Targets

Wed, 19 Feb 2014 17:29:50 GMT

Ouch! So sorry I have QlikView Personal edition and I can't open the file! Can you attach screenshots of the chart config tabs? or a short description of the solution that I'll try to replicate. Thank you a lot! Giuseppe

Re: Chart with Monthly Trend and Targets

Gysbert_Wassenaar — Wed, 19 Feb 2014 17:36:38 GMT

Create a new chart. Choose Combo Chart as chart type. Add Month as dimension and add two expressions:

Number of Events: count(DISTINCT [Event Description])
Cumulative Target: sum([Cum Target])

Set the Display option to Bar for the Number of Events expression.

Set the Display option to Line for the Cumulative Target expression.

Enable the option Full Accumulation for the Number of Events expression.

Add a listbox for the Site field so you can select sites.

Re: Chart with Monthly Trend and Targets

Thu, 20 Feb 2014 08:39:19 GMT

Thank you a lot, it works perfectly.

I need another small tweak.

In my real table 1 I have an additional column named "Year" that contains the year number relevant to the month during which the event happened at a specific site.

What happens is that if I filter the "year" field for 2014 (as an example) and I have data only for months 1 and 2 (Jan and Feb), the chart will remove from the x axis all the other months leaving only two bars and a line segment connecting the target point values of Jan and Feb.

Instead, I'd like to still have all the 12 months on the x axis in order to display the two bars relevant to Jan and Feb performance and the full trendline for the cumulative target from Jan to Dec of the selected year.

I hope the description of the problem is clear enough.

Thank you for the excellent support!

Giuseppe

Re: Chart with Monthly Trend and Targets

Gysbert_Wassenaar — Thu, 20 Feb 2014 08:53:41 GMT

Try adding an extra expression sum({<Year=>}1). Uncheck all Display options for the expression so it won't show in the chart. The {<Year=>} will make sure that the expression ignores selections in the Year field.

Re: Chart with Monthly Trend and Targets

Thu, 20 Feb 2014 09:05:11 GMT

well, now filtering on 2014 (that has events in months 1 and 2 only) correctly leaves the x axis with all the 12 months.

However:

bar data from table 1 is displayed correctly on months 1 and 2
the line chart only consider data from months 1 and 2 also. From the filtered data table (table 2) I can see that when 2014 is selected, data in table 2 is filtered on months 1 and 2 only. Therefore the x axis is ok but such filter prevents from displaying all the points/months in the line chart

Let me know if you have any further idea...

Thank you.