Fetch Current Month Sales

[Anonymous]

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

[devarasu07]

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,

also check this article,

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

[satishkurra]

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)

[Anonymous]

Hello,

Thank You for the prompt reply, but i'm not ale to get the desired output

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 the MTD Sales & YTD Sales

[Anonymous]

Hello,

Thank You for the prompt reply, but i'm not ale to get the desired output

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 the MTD Sales & YTD Sales

[Anonymous]

For Method 1 it is giving 0 value as output