5 Replies Latest reply: Jan 18, 2018 12:27 AM by Ankit Kansara RSS

    Fetch Current Month Sales

    Ankit Kansara

      Hello,

      0down votefavorite

       

       

      I have Sales Register and i want to fetch the Current Month Sales in Qliksense On Basis of Invoice Date in my excel data. So how to fetch the current month sales, ytd & previous day sales

       

      Sample Date is given below

      Invoice Date | Weight

      01/04/2017 | 500

      ....

      17/01/2018 | 250

       

      On the above given data i need to find thePrevious Day,  MTD Sales & YTD Sales

        • Re: Fetch Current Month Sales
          Devarasu R

          Hi

          try below methods,

          Method 1:

          =sum({$<Year={"$(=Max(Year))"},Date={"=$(MonthStart(max(Date)))"}>} Sales)

           

          Method 2:  Using master calendar script

          once added below master calendar then try to use below set analysis expression for current month

          =SUM({$<Year={'$(vCurrYear)'},Month={'$(vCurrMonth)'}>}qty)


          T_MinMax:
          LOAD
             Min(FieldValue('Date',RecNo())) as MinDate,
             Max(FieldValue('Date',RecNo())) as MaxDate
          AutoGenerate FieldValueCount('Date');
          
          LET vMinDate = num(Peek('MinDate'));
          LET vMaxDate = num(Peek('MaxDate'));
          LET vToday = vMaxDate;
          DROP Table T_MinMax;
          
          MasterCalendar:
          LOAD CalDate as Date,
             Day(CalDate) as Day,
              Week(CalDate) as Week,
             Month(CalDate) as Month,
             WeekDay(CalDate) as WeekDay,
             'Q' & Ceil(Month(CalDate)/3) as Quarter,
             Date(MonthStart(CalDate), 'MM-YYYY') as MonthYear,
              Year(CalDate) as Year;
          LOAD Date($(vMinDate) + IterNo()) as CalDate
          AutoGenerate 1
          While $(vMinDate) + IterNo() <= $(vMaxDate);
          
          LET vCurrYear = year($(vMaxDate));
          LET vPrevYear = vCurrYear - 1;
          LET vCurrMonth = month($(vMaxDate));
          LET vPrevMonth = Month(MonthStart(AddMonths($(vMaxDate),-1)));
          
          
          

           

          FYi, output looks like below,

          Capture.JPG

          also check this article,

          https://community.qlik.com/docs/DOC-4313

          • Re: Fetch Current Month Sales
            Satish Kurra

            ALso another way if you do not have master calendar.

             

            In load script after Invoice Date use below line of code

             

            -InMonthToDate(InvoiceDate, MonthEnd(Today(),0), 0) as ThisMonth,

             

            Make sure you prefix with - as above

             

            ThisMonth column returns 1 and 0.

             

            In expression, simply write Sum({<ThisMonth={1}>}Sales)