9 Replies Latest reply: Jul 6, 2016 2:48 PM by Vineeth Pujari RSS

    Help

    Gourav Sikka

      Hi Team,

       

      I am stuck in one requirement which is not able to implement by me. I need your help here.

      my requirement is :-

      we have four fields in our spreadsheet:-

      1. Division

      2. Year

      3. Revenue

      4. Category

       

      in Division field , we have 4 values(CORP,ESM,SAS,ESC) And Year value is {2014,2015 and 2016} with revenue amount is given.

       

      But For "ESM Division" , we are not getting the "2016" year and revenue value data from spreadsheet data. And i want to show the revenue amount for "ESM Division" for 2016 year in qlikview which is the cumulative Revenue value of 2015 and 2014.

       

      And when i select ESM Division as selection filter, then In Bar Chart ..Three BAR Piece should be shown :- one is for 2014 , 2nd is for 2015 and third is for 2016(which would be the cumulative revenue amount of 2014 and 2015 year).

       

      Also i have attached the spreadsheet for the same data.

       

      please check and help me over this.

       

      Thanks

      Gourav

        • Re: Help
          Thirumala Dandamudi

          May be as attached:

          Data:

          LOAD Year,

               Division,

               Revenue,

               Category

          FROM

          [C:\Temp\Community\Division.xlsx]

          (ooxml, embedded labels, table is Sheet1);

           

          Join

          Load

          '2016' as Year,

          'ESM' as Division,

          'I&B' as Category,

          Sum(Revenue) as Revenue

          Resident Data

          Where Division = 'ESM';

          • Re: Help
            Vineeth Pujari

            Hi Gourav,

             

            Please see attached QVW , hopefully this helps you.

             

             

             

            TAB_MAX_YEAR:

            LOAD max(Year) as MAXYEAR

            FROM

            [C:\Users\Vini\Desktop\Data.xlsx]

            (ooxml, embedded labels, table is Sheet1);

             

            Let vMaxYear = NUM(Peek('MAXYEAR',0,'TAB_MAX_YEAR'));

             

             

            LOAD Year,

                 Division,

                 Category,

                 Revenue

                

            FROM

            [C:\Users\Vini\Desktop\Data.xlsx]

            (ooxml, embedded labels, table is Sheet1)

            Where Year < $(vMaxYear);

             

             

            CONCATENATE

             

             

            LOAD $(vMaxYear) as Year,

                 Division,

                 Category,

                 SUM(Revenue) as Revenue

            FROM

            [C:\Users\Vini\Desktop\Data.xlsx]

            (ooxml, embedded labels, table is Sheet1)

            Where Year < $(vMaxYear)

            Group By 

                 Division,

                 Category;

              • Re: Help
                Gourav Sikka

                Hi Sir,

                 

                Suppose in place of revenue we have employee id. and count of employee id for max year show 1..But i want to cumulative count of 2014 and 2015 employee id..how is it possible...kindly help me for solving this requirement.

                  • Re: Help
                    Vineeth Pujari

                    I've made my own sample data, hope it helps.

                    AccumulateEMPCount.JPG

                     

                    TAB_MAX_YEAR:

                    LOAD max(Year) as MAXYEAR

                    FROM

                    [C:\Users\vp51284\Downloads\Data (1).xlsx]

                    (ooxml, embedded labels, table is Sheet1);

                     

                     

                    Let vMaxYear = NUM(Peek('MAXYEAR',0,'TAB_MAX_YEAR'));

                     

                    EMPID_COUNT:

                    LOAD Distinct EmpID,

                      Year,

                      Division,

                      Category,

                      1 as EMPCOUNT

                     

                     

                    FROM

                    [C:\Users\vp51284\Downloads\Data (1).xlsx]

                    (ooxml, embedded labels, table is Sheet1)

                    Where Year < $(vMaxYear);

                     

                     

                    CONCATENATE

                     

                     

                    LOAD $(vMaxYear) as Year,

                        Division,

                        Category,

                        SUM(EMPCOUNT) as EMPCOUNT

                    RESIDENT EMPID_COUNT

                    Where Year < $(vMaxYear)

                    Group By

                        Division,

                        Category;

                • Re: Help
                  Digvijay Singh

                  May be like this -

                  Capture.JPG