Set analysis for amountclass or case logic

[madhuqliklondon]

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.

[sunny_talwar]

May be something like this

Dimension

Aggr(If(Sum(Amount) < 15, 'Under 15', 'Over 15'), [order No])

Expression

Count(DISTINCT [order No])

Sum(Amount)

....