Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

kansaramankit
New Contributor

Fetch Current Month Sales

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

Tags (1)
5 Replies
devarasu07
Honored Contributor II

Re: Fetch Current Month Sales

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

satishkurra
Valued Contributor II

Re: Fetch Current Month Sales

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)

kansaramankit
New Contributor

Re: Fetch Current Month Sales

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

kansaramankit
New Contributor

Re: Fetch Current Month Sales

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

kansaramankit
New Contributor

Re: Fetch Current Month Sales

For Method 1 it is giving 0 value as output

Community Browser