7 Replies Latest reply: Jan 19, 2017 2:20 AM by Manish Kachhia RSS

    Monthly YTD break into MTD/Monthly values in qlikview script

    prithvi k

      Hi ,

       

      I have requirement to break month YTD value into MTD value .I will get 12 months YTD value for year , how to break into Monthly value .

      For example:

       

      Monthly YTD value

       

      Jan - 50

      Feb-100

      March- 120

      April-150

      May - 160

      ...

       

      Dec-200

       

      Break it as Monthly

       

      Jan - 50

      Feb-50(100-50)

      March- 20(120-50-50)

      April-30(150-20-50-50)

      May -30 (160-30-30-50-50)

      ...

       

      Dec-200(Dec-Nov-OCT-Sep-Aug-July-June-May-Aprl-Mar-Feb-Jan)

       

      This  code should be implemented in qlikview script only .

       

      Please help .

       

      Thanks,

      Prithvi

        • Re: Monthly YTD break into MTD/Monthly values in qlikview script
          Sunny Talwar

          Like this

           

          Table:

          LOAD Month(Date#(Month, 'MMM')) as Month,

            Value;

          LOAD * INLINE [

              Month, Value

              Jan, 50

              Feb, 100

              Mar, 120

              Apr, 150

              May, 160

              Jun, 165

              Jul, 170

              Aug, 175

              Sep, 180

              Oct, 188

              Nov, 192

              Dec, 200

          ];

           

          FinalTable:

          LOAD Month,

            Value,

            RangeSum(Value, -Previous(Value)) as [MTD Value]

          Resident Table

          Order By Month;

           

          DROP Table Table;

          • Re: Monthly YTD break into MTD/Monthly values in qlikview script
            Manish Kachhia
            Data:
            Load
              Date#(Left(Month,3),'MMM') as Month,
              Value
            Inline
            [
              Month, Value
              Jan, 50
              Feb, 100
              March, 120
              April, 150
              May,   160
            ];
            
            
            Load
              Month,
              Value,
              If(RowNo()=1,Value,RangeSum(Value, -Peek('Value'))) as NewValue
            Resident Data
            Order By Month;
            
            
            Drop Table Data;
            
            
              • Re: Monthly YTD break into MTD/Monthly values in qlikview script
                prithvi k

                Hi ,

                 

                Thanks for quick reply , your code works if its single record but my requirement is slightly different.

                I'm fetching data  from Qvd files not inline table . Please help

                 

                Please see code here

                 

                Table:

                LOAD Date#(Month, 'MMM YYYY') as Month,

                  Value;

                LOAD * INLINE [

                    Month, Value

                    July 2017, 4754

                    July 2017, 4000

                    Aug 2017, 3997

                    Aug 2017,3000

                    Sep 2017, 6997

                    Oct 2017, 6997

                    Nov 2017, 6997

                    Dec 2017, 6997

                ];

                 

                 

                Temp:

                LOAD Month,

                     Value,

                     RangeSum(Value, -Previous(Value)) as [MTD Value],

                     If(RowNo()=1,Value,RangeSum(Value, -Peek('Value'))) as NewValue

                Resident Table

                Order by Month;

                 

                Drop table Table;

                 

                How to aggregate data here ?

                 

                Thanks,

                Prithvi

                  • Re: Monthly YTD break into MTD/Monthly values in qlikview script
                    Manish Kachhia

                    First of all you need to learn what is the different types of loads available in Qlik.

                    If you are not able to convert Inline load to QVD load (which is quite easy), I strongly suggest you to provide QVD only.

                    Check below code which could be the one you are looking for.

                     

                     

                    Data:

                    Load Month, SUM(Value) as Value Group By Month;

                    LOAD

                      Date#(Month, 'MMM YYYY') as Month,

                      Value

                    INLINE

                    [

                        Month, Value

                        July 2017, 4754

                        July 2017, 4000

                        Aug 2017, 3997

                        Aug 2017,3000

                        Sep 2017, 6997

                        Oct 2017, 6997

                        Nov 2017, 6997

                        Dec 2017, 6997

                    ];

                     

                    Final:

                    Load

                      Month,

                      Value,

                      If(RowNo()=1,Value,RangeSum(Value, -Peek('Value'))) as NewValue

                    Resident Data

                    Order By Month;

                     

                     

                    Drop Table Data;