16 Replies Latest reply: Jun 28, 2012 7:40 AM by Gaurav Malhotra RSS

    Help for Task

    Gaurav Malhotra

      Hi there,

       

      Firstly download & open the file i attached.

       

      then i want to know if its possible to find "Stock" & if yes then how ???

       

      The scenario:

       

      I am having fields:

      A,B,C,D,E,F,PrimarySales, SecondarySales

       

      Given:

       

      I have OpeningStock for 1st month of fiscal year i.e. July

      I have PimarySales & SecondarySales of every month in a year.

       

      Required:

       

      For July,

      The RemainingStock should be calculated by:

      RemainingStock = (OpeningSales+PrimarySales) - SecondarySales

       

      For August,

      RemainingStock of July = OpeningStock of Aug

       

      Regards,

      Gaurav Malhotra

        • Re: Help for Task
          Gaurav Malhotra

          Does Nobody knows what am i talking

          • Re: Help for Task
            Jonathan Dienst

            Gaurav

             

            Please see the attached. I have updated the load script and added a new Stock Report table to the Main tab. Is this what you need?

             

            Regards

            Jonathan

              • Re: Help for Task
                Gaurav Malhotra

                Hi Jonathan,

                My company is not having spare CALs, therefore I am using Personal Edition, so pls share the code

                  • Re: Help for Task
                    Prasath Palani

                    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';

                     

                    Calendar:

                    Load Dual(Month, RowNo()) As Month, Quarter Inline

                    [Month, Quarter

                    Jan, Q4

                    Feb, Q4

                    Mar, Q4

                    Apr, Q1

                    May, Q1

                    Jun, Q1

                    Jul, Q2

                    Aug, Q2

                    Sep, Q2

                    Oct, Q3

                    Nov, Q3

                    Dec, Q3];

                     

                    Sales:

                    Load *, Month(MakeDate(FYear, Period)) As Month Inline

                    [A, B, C, D, E, F, G, Period, FYear, PrimarySales, SecondarySales

                    P, Q, R, S, PQ, RST, 1, 07, 2012, 500, 25

                    P, Q, R, S, QP, TSR, 1, 08, 2012, 100, 30

                    P, Q, R, S, PP, RRS, 1, 09, 2012, 130, 40

                    P, Q, R, S, QQ, SST, 1, 10, 2012, 80, 10];

                     

                     

                    Join(Sales)

                    LOAD Month,

                        FYear,

                        Alt(Peek('ClosingBalance'), 0) As OpeningBalance,

                        //Peek('ClosingBalance') + Sum(PrimarySales) - Sum(SecondarySales) As ClosingBalance

                        Alt(Peek('ClosingBalance'), 0) + Sum(PrimarySales)  - Sum(SecondarySales) As ClosingBalance

                    Resident Sales

                    Group By FYear, Month

                    Order By FYear, Month;

                      • Re: Help for Task
                        Gaurav Malhotra

                        Please Explain the code segments which are color coded:

                         

                        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';

                         

                        Calendar:

                        Load Dual(Month, RowNo()) As Month, Quarter Inline

                        [Month, Quarter

                        Jan, Q4

                        Feb, Q4

                        Mar, Q4

                        Apr, Q1

                        May, Q1

                        Jun, Q1

                        Jul, Q2

                        Aug, Q2

                        Sep, Q2

                        Oct, Q3

                        Nov, Q3

                        Dec, Q3];

                         

                        Sales:

                        Load *, Month(MakeDate(FYear, Period)) As Month Inline

                        [A, B, C, D, E, F, G, Period, FYear, PrimarySales, SecondarySales

                        P, Q, R, S, PQ, RST, 1, 07, 2012, 500, 25

                        P, Q, R, S, QP, TSR, 1, 08, 2012, 100, 30

                        P, Q, R, S, PP, RRS, 1, 09, 2012, 130, 40

                        P, Q, R, S, QQ, SST, 1, 10, 2012, 80, 10];

                         

                         

                        Join(Sales)

                        LOAD Month,

                            FYear,

                           Alt(Peek('ClosingBalance'), 0) As OpeningBalance,

                            //Peek('ClosingBalance') + Sum(PrimarySales) - Sum(SecondarySales) As ClosingBalance

                            Alt(Peek('ClosingBalance'), 0) + Sum(PrimarySales)  - Sum(SecondarySales) As ClosingBalance

                        Resident Sales

                        Group By FYear, Month

                        Order By FYear, Month;

                          • Re: Help for Task
                            Gaurav Malhotra

                            A new situation is there now. I have opening Sales for July, 2012.. Now the

                             

                            "ClosingSales of July" = "OpeningSales of Aug"

                            "ClosingSales of Aug" = "OpeningSales of Sep"

                            & so on.

                             

                            Plus I need

                             

                            ClosingSales = (OpeningSales+PrimarySales) - SecondarySales

                             

                            Regards,

                            Gaurav Malhotra

                    • Re: Help for Task
                      Jonathan Dienst

                      I will leave that to Prasath...

                        • Re: Help for Task
                          Gaurav Malhotra

                          Jonathan, pls help.

                            • Re: Help for Task
                              Prasath Palani

                              Hi Gaurav,

                               

                              give me some time i will come with the solution

                               

                               

                              Regards,

                              Prasath

                                • Re: Help for Task
                                  Gaurav Malhotra

                                  Hi All,

                                   

                                  First Pls explain that code segments.

                                   

                                  Regards

                                  Gaurav Malhotra

                                    • Re: Help for Task
                                      Prasath Palani

                                      Hi,

                                       

                                      Actually in your case you want to create the calendar with the month in the sorting order because, defalut sorting order will be jan - dec (1-12), but we want to create the sorting order that Jul - June (1-12).

                                       

                                      Dual() - this function is used to create the months from 1 - 12, which will be used for sorting order.

                                       

                                      Month(Makedate()) - this function is also create the month and year as the same field name.

                                       

                                      where the month from the calendar table and sales table will be linked.

                                       

                                      Peek() - It is one of the good function which will return the previous row field value, for example Stock(Jul) = 200,

                                      when we use Peek(Stock) = Aug, this will be the July stock will be placed here.

                                       

                                      Regards,

                                      Prasath

                                        • Re: Help for Task
                                          Gaurav Malhotra

                                          What is Alt() ??

                                           

                                          Is it for Alternate value???

                                            • Re: Help for Task
                                              Prasath Palani

                                              Hi,

                                               

                                              Alt() is the works as the switch case statments as in programming languages.

                                               

                                              alt(case1[ , case2 , case3 , ...] , else)

                                              The alt function returns the first of the parameters that has a valid number representation. If no such match is found, the last parameter will be returned. Any number of parameters can be used.

                                               

                                              Example:

                                              alt( date#( dat , 'YYYY/MM/DD' ),

                                              date#( dat , 'MM/DD/YYYY' ),

                                              date#( dat , 'MM/DD/YY' ),

                                              'No valid date' )

                                               

                                              Will test if the field date contains a date according to any of the three specified date formats. If so, it will return the original string and a valid number representation of a date. If no match is found, the text 'No valid date' will be returned (without any valid number representation).

                                               

                                              Regards

                                              Prasath

                                  • Re: Help for Task
                                    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