11 Replies Latest reply: May 17, 2013 5:56 AM by Gaurav Malhotra RSS

    Please Help!

    Gaurav Malhotra

      Nobody is there who can solve my problem, or its impossible to do it.

       

      I am restating the problem again:

       

      I have tables:

       

      FiscalCalendar:

      Month
      Quarter
      JanQ4
      FebQ4
      MarQ4
      AprQ1
      MayQ1
      JunQ1
      JulQ2
      AugQ2
      SepQ2
      OctQ3
      NovQ3
      DecQ3

       

      StockSummary:

      ProductCode
      ProductName
      Sizes
      Month
      Year
      PrimarySales
      SecondarySales
      X050ProductASJul201214356
      X050ProductASAug201212867
      X050ProductASSep201215544
      Y100ProductBMJul201223255
      Y100ProductBMAug201254545
      Y100ProductBMSep201289742
      Z150ProductCLJul201233457
      Z150ProductCLAug201212287
      Z150ProductCLSep201286298
      Z150ProductCLOct20129715

       

      OpeningSales:

      ProductCode
      Month
      Year
      OpningSales
      X050Apr20121000
      Y100Apr20121100
      Z150Apr20121200

       

      Problem:

      1. ClosingSales = (OpeningSales+PrimarySales) - SecondarySales

      2. ClosingSales (March) = OpeningSales (April)

      ClosingSales(April, 2012) = OpeningSales(May, 2012)

      & so on.

      ...........................................................................

      ...........................................................................

      & also,

      ClosingSales (Mar, 2013) = OpeningSales (April, 2013)

      & so on.

      ................................................................................

      .................................................................................

       

      I mean it should behave like this.

       

      Regards,

      Gaurav Malhotra

        • Re: Please Help!
          Oleg Troyansky

          I'm not totally sure that I understand the problem...

           

          You use the terminology that belongs to inventory management, such as:

           

          April's closing balance = May's opening balance

          May's Closing Balance = Opening Balance + Receiving transactions - Shipping transactions

          June Opening Balance = May Closing Balance

           

          For inventory balance calculations, I typically recommend pre-calculating the monthly balances in the load script, using functions previous() and peek(). It's possible to calculate the balances on the fly in chart expressions, but it's more complex and heavier on performance.

           

          The same logic isn't quite applicable to Sales. Sales transactions are simply recorded in the month that they happened. April Sales = sum of Sales that were recorded in April, etc...

           

          If this is a GL calculation of GL balances for the account Sales, then you can use the same logic as for the inventory, only you need to define both the Balances and the Transactions at the same level of detail - either including Size or not including Size. Assuming that you have this information by size, the fragment of the load script to calculate monthly balances could look like the following (assuming that you joined the two tables together):

           

          load

             ProductCode,

             Size,

             Year,

             Month,

             OpeningBalance,

             PrimarySales,

             SecondarySales

             if( previous(ProductCode) <> ProductCode or Previous(Size) <> Size , Opening Balance,

                peek(ClosingBalance) + PrimarySales - SecondarySales) as ClosingBalance,

          ...

           

          This is just an example of how to calculate running balances, you need to do all the prep work of getting the data into the same table...

           

          This sort of a problem belongs to the Data Modeling training class, that I wll surely develop next after the Set Analysis class (back to our LinkedIn discussion)

           

          cheers,

           

          Oleg

           

          cheers

            • Re: Please Help!
              Gaurav Malhotra

              It is basically Stock Analysis.

               

              My boss told me that we have Opening Stock from a Table. This Opening Stock is only maintained by our company once i.e. on the 1st month of the fiscal year. & primary Sales & secondary sales are maintained in other table.

               

              Now we can calculate Remaining Stock by Remaining Stock = [Opening Stock + Primary Sales] - Secondary Sales

               

              Now we are having Remaining Stock for 1st Month

               

              Now This Remaining Stock of 1st month should be equal to the Opening Stock of 2nd month.

               

              How I can do it in QlikView.

                • Re: Please Help!
                  Oleg Troyansky

                  Gaurav,

                   

                  yes, so your problem fits the Inventory Balance calculation that I suggested in my earlier message. You need to bring the Opening Balance into the transaction table (using either Join or Mapping), and then reload the table in memory using the RESIDENT load and applying the logic described above. The only thing I forgot to mention is  that the load needs to be sorted by ProductCode, Size, Year, Month:

                   

                  ...

                  ORDER BY

                     ProductCode,

                     Size,

                     Year,

                     Month

                  ;

                • Re: Please Help!

                  Hi Gaurav,

                   

                  please find the Attached ..

                   

                  Hope it helps you..

                   

                  Thanks,

                  Bhaskar

                    • Re: Please Help!
                      Gaurav Malhotra

                      I can't open this qvw as i am using QV Personal Edition. Can you please paste whole thing in text format

                        • Re: Please Help!

                          //Here is the script :

                          M:

                          Mapping

                          LOAD * INLINE [

                              Month, MonthNum

                              Jan, 1

                              Feb, 2

                              Mar, 3

                              Apr, 4

                              May, 5

                              Jun, 6

                              Jul, 7

                              Aug, 8

                              Sep, 9

                              Oct, 10

                              Nov, 11

                              Dec, 12

                          ];

                           

                           

                          A:

                          LOAD ProductCode,

                               ProductName,

                               Sizes,

                               Month,

                               Year,

                               PrimarySales,

                               SecondarySales

                          FROM

                          [Book1.xlsx]

                          (ooxml, embedded labels, table is Sheet1);

                          B:

                          Load *,

                          Date(MakeDate(Year,ApplyMap('M',Month)),'MMM-YYYY') as  YearMonth

                          Resident A;

                           

                          Drop Table A;

                           

                          C:

                          Load

                          *,

                          If(Peek(ProductCode)<>ProductCode ,0,Peek(ClosingBalance)) as OpeningBalance,

                          If(Peek(ProductCode)=ProductCode,Peek(ClosingBalance)+(PrimarySales - SecondarySales),(PrimarySales - SecondarySales)) as ClosingBalance

                          Resident B Order by ProductCode,YearMonth;

                          Drop Table B;

                    • Re: Please Help!
                      Gaurav Malhotra

                      SET NullDisplay="";

                      NULLASVALUE *;

                      SET ThousandSep=',';

                      SET DecimalSep='.';

                      SET MoneyThousandSep=',';

                      SET MoneyDecimalSep='.';

                      SET MoneyFormat='$#,##0.00;($#,##0.00)';

                      SET TimeFormat='h:mm:ss TT';

                      SET DateFormat='M/D/YYYY';

                      SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

                      SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

                      SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

                       

                       

                      Transactions:

                       

                       

                      LOAD [Material Code],

                           Material,

                           [Depo Location],

                           Year,

                           Month,

                           [Opening Stock]

                      FROM

                      [C:\Users\test\Desktop\Secondary Sales\QVD Gen\OpeningStock.qvd]

                      (qvd);

                      Join LOAD [Material Code],

                           Material,

                           [Depo Location],

                           Year,

                           Month,

                           [Primary Sales]

                      FROM

                      [C:\Users\test\Desktop\Secondary Sales\QVD Gen\PrimarySales.qvd]

                      (qvd);

                      Join LOAD [Material Code],

                           Material,

                           [Depo Location],

                           Year,

                           Month,

                           [Secondary Sales]

                      FROM

                      [C:\Users\test\Desktop\Secondary Sales\QVD Gen\SecondarySales.qvd]

                      (qvd);

                       

                       

                      MasterData:

                      LOAD [Plant Code],

                           [Sales Organisation],

                           [Depo Location],

                           Division

                      FROM

                      [C:\Users\test\Desktop\Secondary Sales\QVD Gen\MasterData.qvd]

                      (qvd);

                       

                       

                      Mapping LOAD [Material Code],

                           Category

                      FROM

                      [C:\Users\test\Desktop\Secondary Sales\Customised Sheets\Rajasthan\MappingTable.xlsx]

                      (ooxml, embedded labels, table is Mapping);

                       

                       

                      Load [Material Code],

                                Month & Year as MonthYear,

                                If(Peek([Material Code])=[Material Code],Peek([Remaining Stock])) as [Opening Stock],

                                If(Peek([Material Code])=[Material Code],Peek([Remaining Stock])+([Primary Sales] - [Secondary Sales]),([Primary Sales] - [Secondary Sales])) as [Remaining Stock]

                                Resident Transactions

                                Order by Month, Year, [Material Code];

                       

                      What is wrong in it ???

                      • Re: Please Help!
                        Pablo Labbe

                        Gaurav,

                         

                          If you are a beginner and are trying to do advanced and urgent stuff, the community in this forum can´t help you in required time. All of us are here to share and collaborate not for free consulting.

                         

                           Have you get a Qlikiview Training first ?  There is somebody in your company that have mores Qlikview Skills that could help you ?  Your company can afford for consulting services for a few days and help you ?

                         

                          Community is open to help, we are here to teach you to fish rather than give you the fish. Maybe someone can give you the fish, but could take a while.

                         

                        Take Care,

                         

                        Pablo Labbe

                          • Re: Please Help!
                            Gaurav Malhotra

                            Hi there,

                             

                            Thanks a lot for all of you. I finished the Inventory/Stock Analysis Application few days back with all your help. Credit goes to all of you. Most importantly, Mr. Bhaskar Reddy & Mr. Oleg Tyransky. Sorry fo being rude as i was doing whole thing in a kind of urgency & needed help.

                             

                            The correct logic was something like:

                             

                            If(Peek(Key)<>Key ,test23,Peek([Remaining Stock])) as [Opening Stock],

                            If(Peek(Key)<>Key,(test23+([Primary Sales] - [Secondary Sales])),Peek([Remaining Stock])+([Primary Sales]-[Secondary Sales])

                            Resident TransactionsTemp ORDER BY Key, Year, MonthNum;

                             

                             

                            Regards Gaurav Malhotra

                          • Re: Please Help!
                            Rajni Batra

                            Can anybody plz help me with this stock

                             

                              GRN - GI = Closing

                            closing + GRN = next month opening

                            againg Opening -GI = closing for same month