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

    Fetch Current Month Sales

    Ankit Kansara


      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


          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


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


          also check this article,


          • 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)