count total customer who placed orders

[#1]

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

[ajsjoshua]

Hi,

Try this

Aggr(Total(customers),Orders)

Declare previous month.

[avinashelite]

try like this

Aggr(count(CustomerId),OrderDate)

[marjan_it]

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

[vinieme12]

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

[#6]

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?

[vinieme12]

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.

[effinty2112]

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

[vinieme12]

Hi,

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

Thanks

V