CODEWITHSUNDEEP
qliksense
Ankit Kansara
Ankit Kansara

Reputation: 181

Fetch Current Month Sales in Qliksense

I have Sales Register and i want to fetch the Current Month Sales & YTD in Qliksense On Basis of Invoice Date field in my excel data. So how to fetch the current month 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 the MTD Sales & YTD Sales

Upvotes: 0

Views: 723

Answers (1)

Stefan Stoychev
Stefan Stoychev

Reputation: 5012

You have 2 options - flags in the script and on the fly

Flags in the script

create two new fields InMTD and InYTD with values 1 and 0 indicating if the record's InvoceDate is in MTD or YTD (the script below).

After having these two fields your expressions are quite straight forward:

= sum(  {< InMTD = { 1 } >}  Weight )
= sum(  {< InYTD = { 1 } >}  Weight )

The script uses InMonthToDate and InYearToDate functions

Temp_Table:
Load
    InvoiceDate,
    // Create flag if the InvoceDate is in MTD based on Today() value
    InMonthToDate(InvoiceDateId, Today(), 0) * -1  as InMTD,
    // Create flag if the InvoceDate is in YTD based on Today() value   
    InYearToDate(InvoiceDateId, Today(), 0)  * -1  as InYTD,
    Weight
;
Load
    // Create number representation of the data field
    // which will be used in MTD and YTD calculations
    num(date(date#(InvoiceDate, 'DD/MM/YYYY'))) as InvoiceDateId, 
    InvoiceDate,
    Weight
;
// Load the actual data
Load * Inline [
    InvoiceDate , Weight
    01/04/2017  , 500
    10/01/2018  , 250
    12/01/2018  , 250
    13/01/2018  , 250
    17/01/2018  , 250
    19/01/2018  , 250
];

On the fly

Basically uses the same approach but everything is done in the expression

= sum( {< InvoiceDate = {">=$(=MonthStart(Today()))<=$(=Today())"} >}  Weight )
= sum( {< InvoiceDate = {">=$(=YearStart(Today()))<=$(=Today())"}  >}  Weight )

Example file can be downloaded from here

Upvotes: 1

Related Questions