Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements

QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: **REGISTER NOW!**

- Qlik Community
- :
- Forums
- :
- Analytics
- :
- New to Qlik Sense
- :
- Re: aggr in set analysis compare with set value

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

ElsKnockaert

Contributor III

2021-05-07
04:14 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

aggr in set analysis compare with set value

Hello everybody

I have an issue with

#BC : =count (distinct Cust_Id)

% Repeat Purchase: = (count({$}distinct Order_Id) - column(1) )/ column(1)

#Orders : =count({$}distinct Order_Id)

What I want:

In the column % Repeat Purchase I want to see the % of customers who have had more than 1 order linked to them.

Current:

Doesn't take into consideration that a customer could also have 3 orders. In which case the % should stay at 2.27%, but now it would be 4.55% .

I've tried (and many variantions) but it's not working:

count({$< aggr(count(Order_Id), Cust_Id)={">1"} >} , Cust_Id)

377 Views

1 Solution

Accepted Solutions

OmarBenSalem

MVP

2021-05-07
05:22 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

I'll assume this is it :

your measure would be something like

**count(distinct {<CustID={"=count(distinct Orders)>1"}>} CustID)/count(distinct CustID)**

which results in :

324 Views

6 Replies

OmarBenSalem

MVP

2021-05-07
04:24 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Can you present your date in a simple way? maybe an inline table with 6 lines? and explain with them the result needed? It would be much easier to help when we have data to work with !

Thanks

369 Views

ElsKnockaert

Contributor III

2021-05-07
04:56 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Of course! I hope this helps:

I would like a 3rd column next to #Orders that shows a % of how many distinct customers have had more than 1 order.

In this example, User1 has 44 distinct customers who have placed an order. But 45 Orders have been made in total, so 1 customer has placed 2 orders. so in the 3rd column I would like to see [amount of distinct customers with multiple orders] / [amount of distinct customers with at least 1 order = column(1)]. This would be 2.27% in the case of User1.

If I just do (#orders-column(1) ) / column(1) , it doesn't take into account that 1 customer could have placed 3, 4, 5, or more orders.

If User1 has 44 customers with at least 1 order, but placed 46 orders in total.

Of which 3 at the same customer, then I would like the statement to take that 1 customer with more than 1 order, divide by 44. Giving 2.27% and not 4.55% as it would with the present statement.

For the other Users, they have as many orders as customers with at least 1 order, so stands to reason that the 3rd should have to be 0 for them or NULL.

345 Views

OmarBenSalem

MVP

2021-05-07
05:07 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

which information gives you how many orders were placed by a customer?

What I'm interested to know is how your data is created?

Let me try to explain what I've understood :

Employee1 has 5 distincts customers, customer 5 has placed 3 orders , so the total nb of order for Emp 1 would be 7 ! but their was only 1 customer with multiple orders, so 1/5 not 1/7? right?

Employee2 has 5 distincst, but cust 1 and 2 has each 2 orders so the % would be 2/5 not 2/7?

EMployee3 has 2 customers with 2 orders so 0/5?

If this is it, I'll try to create an inline table with this and come back later with an answer (hopefully)

338 Views

ElsKnockaert

Contributor III

2021-05-07
05:19 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

That's exactly right!

328 Views

OmarBenSalem

MVP

2021-05-07
05:22 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

I'll assume this is it :

your measure would be something like

**count(distinct {<CustID={"=count(distinct Orders)>1"}>} CustID)/count(distinct CustID)**

which results in :

325 Views

ElsKnockaert

Contributor III

2021-05-07
06:27 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

That's it! Thank you so much!

304 Views