Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Need Help to create a chart

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 .

EmployeeSt DateEnd DateSt Date AmountEnd Date Amount
A2011Q12012Q1100300
2011Q22012Q2200550
2011Q32012Q3300700
2011Q42012Q4400150
2012Q12013Q1500400
2012Q22013Q2600200
2012Q32013Q3700500
2012Q42013Q4800350
B2011Q12012Q1100300
2011Q22012Q2230550
2011Q32012Q3300700
2011Q42012Q4470150
2012Q12013Q1580400
2012Q22013Q2620200
2012Q32013Q3780500
2012Q42013Q4810350

I need to create the output report in Qlikview:-

PeriodSt Dt AmountEnd Dt Amount
2012Q11080700
2012Q21220750
2012Q314801200
2012Q41610500
2 Replies
Not applicable

Re: Need Help to create a chart

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:

EmployeeTypeDateAmount
AStart2011Q1100
AStart2011Q2200
AEnd2012Q2550
BStart2011Q1300
BEnd2012Q1550

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.

EG

expression for St Dt Amount would be

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

Hope this helps,

Regards,

Erica

suresh_rawat
Contributor II

Re: Need Help to create a chart

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.

PeriodSt Dt AmountEnd Dt Amount
2012Q11080600
2012Q212201100
2012Q314801400
2012Q41610300


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