Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. Register by February 29th to save $200. Learn More
Highlighted
madhuqliklondon
Contributor II

Set analysis for amountclass or case logic

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.

1 Reply
Highlighted

Re: Set analysis for amountclass or case logic

May be something like this

Dimension

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

Expression

Count(DISTINCT [order No])

Sum(Amount)

....