Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

saadeddine
New Contributor

How to get this month and last month sales total within a KPI measure

Hello all,

I run a small store and I am new to qlick cloud.

I want to create a KPI that calculates total sales of current month in a separate measure and last month in another measure.

sample table as following

InvoiceDateInvoiceTotal
9/15/2017100
9/15/201755
9/12/201722
9/5/201788
8/25/201782
8/15/201742
8/12/201755
8/8/201775
8/2/201718

using dimensions with in charts and tables its easy but I cant figure out how to do this in a KPI chart

Tags (1)
1 Solution

Accepted Solutions
aqu
Contributor II

Re: How to get this month and last month sales total within a KPI measure

Assuming that autocalendar has created dates for You You only need to go to Edit mode/Fields and rightclick on the date field (invoice date in this case and select "Create Calendar measures";

create.fields.png

Then measures will be created under Master items/Measures that You can then use for KPI's etc;

Measures.png

Also see help section https://help.qlik.com/en-US/sense/June2017/Subsystems/Hub/Content/Measures/create-calendar-measure.h...

BR

//Lucke

5 Replies
itsakshaye
New Contributor III

Re: How to get this month and last month sales total within a KPI measure

Hi  Naseem,

For current month sale,

Sum({< InvoiceDate={">=MonthStart(Max(InvoiceDate))<=Max(Invoice Date)"} InvoiceTotal >})

For previous month sale,

Sum({< InvoiceDate={">= AddMonths(MonthStart(Max(InvoiceDate)),-1)<MonthStart(Max(InvoiceDate))"} InvoiceTotal >})


Thanks,

Akshay

saadeddine
New Contributor

Re: How to get this month and last month sales total within a KPI measure

Hi Akshay,

For some reason I am getting "Error in expression" in both measures. cant figure out what is miss typed.

itsakshaye
New Contributor III

Re: How to get this month and last month sales total within a KPI measure

Hey Naseem ,

Try below code,

SET DateFormat='MM/DD/YYYY';

LOAD InvoiceDate, InvoiceTotal INLINE [
InvoiceDate, InvoiceTotal
9/15/2017, 100
9/15/2017, 55
9/12/2017, 22
9/5/2017 , 88
8/25/2017, 82
8/15/2017, 42
8/12/2017, 55
8/8/2017 , 75
8/2/2017 , 18
]
;

Create below variables,

1) vMaxMonthStartDate   -->>     =AddMonths($(vMaxMonthStartDate),-1,1)

2) vMaxDate                       -->>    =Date(Max(InvoiceDate),'MM/DD/YYYY')

3) vPrevMonthStartDate   -->>    =Date(MonthStart(Max(InvoiceDate)),'MM/DD/YYYY')

For Current Month Sales :

=Sum({<InvoiceDate={">=$(vMaxMonthStartDate)<=$(vMaxDate)"}>}InvoiceTotal)

For Previous Month Sales :

=Sum({<InvoiceDate={">=$(vPrevMonthStartDate)<$(vMaxMonthStartDate)"}>}InvoiceTotal)

Thanks,

Akshay Navale

aqu
Contributor II

Re: How to get this month and last month sales total within a KPI measure

Assuming that autocalendar has created dates for You You only need to go to Edit mode/Fields and rightclick on the date field (invoice date in this case and select "Create Calendar measures";

create.fields.png

Then measures will be created under Master items/Measures that You can then use for KPI's etc;

Measures.png

Also see help section https://help.qlik.com/en-US/sense/June2017/Subsystems/Hub/Content/Measures/create-calendar-measure.h...

BR

//Lucke

saadeddine
New Contributor

Re: How to get this month and last month sales total within a KPI measure

This is an amazing reply. and yes I was able to get what I want. thank you