5 Replies Latest reply: Sep 7, 2017 5:34 AM by An Pham RSS

    How to show item not sale in month.

    An Pham

      Dear All

      I want to show items not show in month in table and KPI (Count item not sale in month)

      Here my sample data

       

      SaleTBL:
      load
      *
      Inline
      [
      Item ,Month,Sales
      ABC,FEB,10
      ABC,MAR,30
      ABC,APR,10
      DEF,JAN,10
      DEF,FEB,20
      DEF,APR,80
      ];
      
      MonthTBL:
      load
      *
      Inline
      [
      Month
      JAN
      FEB
      MAR
      APR
      ];
      

      In table i want to show all items include items not sale in JAN (ABC) and MAR( DEF) like this

       

      ITemMonthSum(Sales)
      ABCJAN- or 0 (Item not sale in JAN)
      ABCFEB10
      ABCMAR30
      ABCAPR10
      DEFJAN10
      DEFFEB20
      DEFMAR- or 0 ( Item Not Sale in MAR)
      DEFAPR80

      Regards

        • Re: How to show item not sale in month.
          Anand Chouhan

          Try with this

           

          SaleTBL:

          load  *  Inline

          [

          Item ,Month,Sales

          ABC,FEB,10

          ABC,MAR,30

          ABC,APR,10

          DEF,JAN,10

          DEF,FEB,20

          DEF,APR,80

          ];

           

          Left Join(SaleTBL)

          MonthTBL:

          load  *  Inline

          [

          Month

          JAN

          FEB

          MAR

          APR

          ];

           

          Noconcatenate

          Finaltab:

          Load

          *,If( Sales = 0 Or isnull(Sales) = -1 OR Sales = '-', 0,Sales) as ReviseSales

          Resident SaleTBL;


          Drop table SaleTBL;


          On UI Part


          Dim:- Item ,Month

          Expression :- Sum(ReviseSales)

            • Re: How to show item not sale in month.
              An Pham

              Hi

              Thanks for your response
              your solution working well but because my data is very large, i don't want to join two tables

              i want to implement it on Chart

              Regards

                • Re: How to show item not sale in month.
                  Anand Chouhan

                  You have to create the Missing data in your main table that is SaleTBL because from this table you have to do calculation. Because in the Months you don't have continue months Jan,Feb,Mar........

                  • Re: How to show item not sale in month.
                    Antonio Mancini

                    Hi,

                    You don't join two tables.

                    MonthTBL:
                    LOAD Min(Month) as MinMonth,Max(Month) as MaxMonth;
                    load Month(Date#(Month,'MMM')) as Month Inline
                    Month 
                    JAN 
                    FEB 
                    MAR 
                    APR 
                    ]

                    LET vMinMonth=Peek('MinMonth');
                    LET vMaxMonth=Peek('MaxMonth');

                    SaleTBL: 
                    load Item,Month(Date#(Month,'MMM')) as Month,Sales Inline
                    Item ,Month,Sales 
                    ABC,FEB,10 
                    ABC,MAR,30 
                    ABC,APR,10 
                    DEF,JAN,10 
                    DEF,FEB,20 
                    DEF,APR,80 ]
                    ;
                    Join LOAD DISTINCT Item,$(vMinMonth)+IterNo()-1 as Month
                    Resident SaleTBL
                    While $(vMinMonth)+IterNo()-1 <= $(vMaxMonth)
                    ;

                     

                     

                    Regards,

                    Antonio

                      • Re: How to show item not sale in month.
                        An Pham

                        Thanks Antonio

                        This is my sample data, in fact there are many data column (Dimension) in table SaleTBL (Date,Region, Province, District, SaleManager,Saleman, Store (200k Store)  ), and ~100 Items (~40 not sale by day)

                        Transaction data 1.5Mil/Day

                        I want to show Items not sale in month and group by each Dimension above

                        if join table as you solution, size app will be increase

                        Regards