Set analysis help

[madhuqliklondon]

I have columns :

[customer NO]

[order number]

[total amount]

[date]

[country]

what I am trying to get is Per month How many  distinct customers( because one customer may order couple of times with different order number) , how many orders are there under £20 and how many are over £20 , total revenue and average order value

some thing like below .

|  

group	total customers	total orders	£total revenue	£aov
under £20	98721	13587	254712.23	18.74
over 20	12847	19587	878587.23	44.85

[Sergey_Shuklin]

Hi, Madhu!

Count(distinct [customer NO])

Count({<[total amount]={"<20"}>}[order number])

Count({<[total amount]={">=20"}>}[order number])

Sum(Total [total amount])

AVG([total amount])

The one thing I didn't get is [total amount] - is amount of one order or all orders?

[Sergey_Shuklin]

If you want <20 and >20 like dimension the best way is to create one more column in the script with condition:

if(amount<20,'<20','>=20') as group. And use it in the report as dimension.

[madhuqliklondon]

Total amount is for Individual order as customer have few lines of items , so its total order amount, in final table total revue is total of all orders under £20 .

[madhuqliklondon]

Where shall write them

Count(distinct [customer NO])

Count({<[total amount]={"<20"}>}[order number])

Count({<[total amount]={">=20"}>}[order number])

Sum(Total [total amount])

AVG([total amount])

[Sergey_Shuklin]

Create a straight or pivot table with Dimension "group" (as I offered above).

Then create

expression 1: Count(distinct [customer NO]) - it's total customers

expression 2: Count([order number]) - it's total orders

expression 3: Sum([total amount]) - it's total revenue

expression 4: AVG([total amount]) - it's aov

[sunny_talwar · Accepted solution]

May be this

Dimension

Aggr(If(Sum([total amount]) < 20, 'under £20', 'over £20'), [order number])

Expressions

Count(DISTINCT [customer NO])

Count(DISTINCT [order number])

Sum([total amount])

Avg(Aggr(Sum([total amount]), [order number]))

[madhuqliklondon]

What is dimension "group"?? how to add it .

[sunny_talwar]

Did you look at my response below?

[madhuqliklondon]

Hi Sunny ,

I am just trying your reply now.