topic Set analysis help in QlikView

Set analysis help

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

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

Re: Set analysis help

Sergey_Shuklin — Wed, 24 May 2017 11:23:56 GMT

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?

Re: Set analysis help

Sergey_Shuklin — Wed, 24 May 2017 11:26:40 GMT

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.

Re: Set analysis help

madhuqliklondon — Wed, 24 May 2017 11:27:19 GMT

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 .

Re: Set analysis help

madhuqliklondon — Wed, 24 May 2017 11:34:24 GMT

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])

Re: Set analysis help

Sergey_Shuklin — Wed, 24 May 2017 11:38:20 GMT

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

Re: Set analysis help

sunny_talwar — Wed, 24 May 2017 11:44:16 GMT

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]))

Re: Set analysis help

madhuqliklondon — Thu, 25 May 2017 11:50:02 GMT

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

Re: Set analysis help

sunny_talwar — Thu, 25 May 2017 12:10:06 GMT

Did you look at my response below?

Re: Set analysis help

madhuqliklondon — Thu, 25 May 2017 12:10:46 GMT

Hi Sunny ,

I am just trying your reply now.

Re: Set analysis help

madhuqliklondon — Thu, 25 May 2017 12:22:09 GMT

Hi Sunny ,

when I use those expressions , it is counting single orders as one and its taking avg of that order only. I was trying to do Total distinct customers, total no of orders and all order total revenue and the avg of revenue for selected month

Re: Set analysis help

sunny_talwar — Thu, 25 May 2017 12:24:31 GMT

Did you use this as your dimension?

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

Re: Set analysis help

madhuqliklondon — Thu, 25 May 2017 12:31:08 GMT

Re: Set analysis help

madhuqliklondon — Thu, 25 May 2017 12:32:32 GMT

Hi Sunny ,I have clicked on amount in user Dimensions and added.

Re: Set analysis help

sunny_talwar — Thu, 25 May 2017 12:45:36 GMT

Not sure I understand... would you be able to share a sample?Preparing examples for Upload - Reduction and Data Scrambling

Re: Set analysis help

madhuqliklondon — Thu, 25 May 2017 12:49:43 GMT

Hi Sunny got it, I never seen top of the column where it is giving totals. Is there a chance we add Dimension to search box like under £15 and Over £15 ? like we add month, year etc..

Re: Set analysis help

madhuqliklondon — Thu, 25 May 2017 13:44:51 GMT

To multi box I mean.