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

[saadeddine]

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

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

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

[akshaye_c_navale]

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]

Hi Akshay,

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

[akshaye_c_navale]

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

[Lucke_Hallbergson · Accepted solution]

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

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

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

BR

//Lucke

[saadeddine]

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