Count of orders as of order value

[madhuqliklondon]

  Hi all

I am trying to count no of orders under certain amount based on the month year. I have created monthyear based on order date and now trying to count orders under £30 ,£40 and so on.

Please find the example below

Data

Order_date	Sample_order_number	Total_amount
12.12.2017	12345	35
15.01.2018	678910	39
25.02.2018	13579	45
Result:
Monthyear	Dec-17	Jan-18	Feb-18
Over £30	1	1	1
Over £40	0	0	1

Thank you

[gerry_hdm]

you a using  Intervallmatch

[madhuqliklondon]

No,I am not using intervalmatch

[MK_QSL · Accepted solution]

Orders:
Load
 *,
 Date(MonthStart(OrderDate),'MMM-YY') as OrderMonthYear
Inline
[
 OrderDate, OrderNumber, OrderAmount
 12/12/2017, 111, 35
 15/01/2018, 222, 39
 20/02/2018, 333, 45
];

KPI:
Load * Inline
[
 KPI
 Over £30
 Over £40
];

Now create a Pivot Table

Dimension

KPI

OrderMonthYear (use this as column by drag and drop of pivot table functionality)

Expression

SUM(Aggr(IF(KPI = 'Over £30' and SUM(OrderAmount) >= 30, 1,

IF(KPI = 'Over £40' and SUM(OrderAmount) >= 40, 1)),OrderNumber,OrderMonthYear,KPI))

QVW enclosed for your reference.

[Anonymous]

Another Expression that can be helpful:

temp:

Load * Inline [

Order_date,Order_no,Amount

'12/12/2017','12345','35',

'01/15/2018','23467','39',

'02/25/2018','6578','45'

];

Data:

Load Order_date,

Order_no,

Amount,

MonthName(Date(Order_date)) as MonthYear

Resident [temp];

Drop Table temp;

Limit:

Load * Inline

[Limit

'30',

'40']

Create Pivot table with:

Row : Limit

Column: MonthYear

Measure: Sum(if(Amount>Limit,1,0))