topic Re: count total customer who placed orders in QlikView

count total customer who placed orders

Sat, 02 Jul 2016 05:31:54 GMT

Hi Guys

In my data model...

Order Table contains CustomerId & OrderDate

And CustomerTable is not a left join so it loads all the customer even if there are no orders

What is the best expression to count total number of customers who placed orders in previous month

Re: count total customer who placed orders

ajsjoshua — Sat, 02 Jul 2016 05:51:57 GMT

Hi,

Try this

Aggr(Total(customers),Orders)

Declare previous month.

Re: count total customer who placed orders

avinashelite — Sat, 02 Jul 2016 05:55:37 GMT

try like this

Aggr(count(CustomerId),OrderDate)

Re: count total customer who placed orders

marjan_it — Sat, 02 Jul 2016 07:36:26 GMT

you can try it

=Count(DISTINCT(if (not IsNull(OrderDate) , CustomerId))

and if you want to get customer in previous month you can add your condition in if cluse

=Count(DISTINCT(if (not IsNull(OrderDate) and OrderDate='previous month', CustomerId))

Re: count total customer who placed orders

vinieme12 — Sat, 02 Jul 2016 08:14:02 GMT

Hi Lokesh,

This will work for you

=sum(aggr(if(count(OrderDate)>0,1),CustomerId))

But I must insist that you make changes to your data model instead.

A better way would be to use ApplyMap to find records in your Orders table and add a Flag to your Customer table .

Happy Qlik'ing

Cheers

Vineeth

Re: count total customer who placed orders

Sat, 02 Jul 2016 10:30:52 GMT

Hi Vineeth,

very good suggestion but I am new to qlikview and don't know how to flag.

There is a reason why I have not joined the customer table because the order table has half a million records. Where as we only have total 15 customers.

So I have simply left the Customer table separate as QlikView automatically joined Orders with Customer based on CustomerID.

Also not all the customers place orders all the time so sometime on the dashboard users should be able to see all the customers.

What are the benefits of Flags? and how to do it?

Re: count total customer who placed orders

vinieme12 — Sat, 02 Jul 2016 10:44:58 GMT

Lets assume the below is your load script, (has to be in the particular order)

//LOAD YOUR Orders TABLE

ORDERS:

LOAD *

FROM SOURCE;

//LOAD DISTINCT CustomerID available in Orders table

MAP_OrderFlag:

Mapping LOAD

DISTINCT CustomerId,

'Ordered' as OrderFlag

RESIDENT ORDERS;

//Now load Customers Table with Applymap

Customers:

CustomerID,

ApplyMap('MAP_OrderFlag',CustomerID,'Not Ordered') as OrderFlag

FROM SOURCE;

ApplyMap works like vlookup in Excel , if it finds a matching customer ID it will retrieve value 'Ordered' if not found, it will place value as 'Not Ordered'.

Benefit:

Now your expression will be : count({<OrderFlag = {'Not Ordered'}>}CustomerID)

This will be evaluated on your customers table with 15 rows instead of the your Orders table which has millions of rows.

Hope I've clarified well

Thanks

Vineeth.

Re: count total customer who placed orders

effinty2112 — Sun, 03 Jul 2016 11:01:03 GMT

Hi Lokesh,

I think a good way to proceed would be to script a master calendar with a numeric MonthID.

that would make writing expressions to give you the answer you want easy but you can still write one that works:

=count({$<Date = {'>=$(=MonthsStart(1,AddMonths(today(),-1)))<=$(=MonthsEnd(1,AddMonths(today(),-1)))'}>}DISTINCT CustomerId])

This will give you the number of customers that ordered during the month previous to the currently selected month.

Cheers

Andrew

Re: count total customer who placed orders

vinieme12 — Fri, 08 Jul 2016 07:13:58 GMT

Hi,

Can you mark helpful/correct answers and close this post.

Thanks