2 Replies Latest reply: Apr 16, 2012 4:31 AM by Suresh Rawat RSS

    Need Help to create a chart

    Ashish Kumar

      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
        • 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

          • Re: Need Help to create a chart
            Suresh Rawat

            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.