Hi I am trying to get a report to find classify orders like we use in SQL, something like this .
table data
Customer no| order No| Amount | Shipping Service
-------------------------------------------------
1 | 254 | 8 | standard
2 | 258 | 12 | Tracked
1 | 260 | 10 | Standard
3 | 285 | 13 | Tracked
4 | 295 | 11 | Standard
sql query :
select amountclass, "Shipping Service",
count(distinct "Customer no") as "Total customers",
count(*) as "Total orders",
sum("Amount") as "total revenue",
avg("Amount" * 1.0) as "AOV"
from
(
select "Customer no", "order No", "Amount", "Shipping Service"
case when "Amount" < 15 then "Under 15"
else "Over 15"
end as amountclass
from tablename
) dt
group by amountclass, "Shipping Service"
order by amountclass, "Shipping Service"
results:
Month Aug 2016
Under 15 | Standard Delivery | Total customer - 2
Total orders - 3
total revenue - £18
AOV - £9
Tracked Delivery | Total customer - 2
Total orders - 2
total revenue - 25
AOV - 12.50
So I have created table and loaded all columns . now I am trying to do same here where I can get how many distinct customers , how many orders , total revenue ,average order value got two filters under £15 orders should be grouped and filtered and over £15 in another ?? how do get there ..set analysis ...help please.
