topic Need Help to create a chart in QlikView

Need Help to create a chart

Fri, 13 Apr 2012 08:13:15 GMT

Hi All,

Please help if anyone have good solution ....

2 dimensions to be combined into single dimension & we will have to show the into the report i gave...

I have the below data .

Employee	St Date	End Date	St Date Amount	End Date Amount
A	2011Q1	2012Q1	100	300
	2011Q2	2012Q2	200	550
	2011Q3	2012Q3	300	700
	2011Q4	2012Q4	400	150
	2012Q1	2013Q1	500	400
	2012Q2	2013Q2	600	200
	2012Q3	2013Q3	700	500
	2012Q4	2013Q4	800	350
B	2011Q1	2012Q1	100	300
	2011Q2	2012Q2	230	550
	2011Q3	2012Q3	300	700
	2011Q4	2012Q4	470	150
	2012Q1	2013Q1	580	400
	2012Q2	2013Q2	620	200
	2012Q3	2013Q3	780	500
	2012Q4	2013Q4	810	350

I need to create the output report in Qlikview:-

Period	St Dt Amount	End Dt Amount
2012Q1	1080	700
2012Q2	1220	750
2012Q3	1480	1200
2012Q4	1610	500

Re: Need Help to create a chart

Sat, 14 Apr 2012 17:10:21 GMT

Hi ashishkumar

There are two ways, but will involve restructuring the script:

1) Make your main data table more "vertical"

By this I mean writing the script so that the StDate and EndDate are now in one column. You could write this easily in the script using a crosstable statement:

Data:

noconcatenate load Employee, [St Date] as Date,[St Amount] as Amount, 'Start' as Type from initaltab;

concatenate(Data) load Employee, [End Date] as Date,[End Amount] as Amount, 'End' as Type from initialtab;

This will result in the following table:

Employee	Type	Date	Amount
A	Start	2011Q1	100
A	Start	2011Q2	200
A	End	2012Q2	550
B	Start	2011Q1	300
B	End	2012Q1	550

This is now very easy to pivot into the table you require.

2) The other alternative would be to create a stand-alone table of dates, eg using inline to create a field called [period] and the values 2011Q1, 2011Q2...etc

You could then use this as the dimension to base your new table on, and use if statments to match the values from the other table in it in expressions.

expression for St Dt Amount would be

= sum(if([St Date] = [period],[St Date Amount]))

Hope this helps,

Regards,

Erica

Re: Need Help to create a chart

suresh_rawat — Mon, 16 Apr 2012 08:31:57 GMT

Hi ashishkumar,

Your Final report should be like this, i think your End Dt Amount is not correct or your requirement is different. Please check it and reply to me what actually you want. According to me this should be the report.

Period	St Dt Amount	End Dt Amount
2012Q1	1080	600
2012Q2	1220	1100
2012Q3	1480	1400
2012Q4	1610	300

For this i am attaching a example, hope this will help you to understand.