Set analysis for amountclass or case logic

madhuqliklondon — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Set analysis for amountclass or case logic

sunny_talwar — Wed, 24 May 2017 11:13:49 GMT

May be something like this

Dimension

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

Expression

Count(DISTINCT [order No])

Sum(Amount)

....

topic Re: Set analysis for amountclass or case logic in QlikView

Set analysis for amountclass or case logic

Re: Set analysis for amountclass or case logic