Skip to main content

New to Qlik Sense

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

Announcements
JAN. 24, Q&A with Qlik: Making the Move to Qlik Cloud Reporting: REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
its_rajvir
Contributor III
Contributor III

General Question

Dear Experts,

we have data example:

Customer ID , Recharge Amount (ex. 5,10,15,20...................................)

I have some questions regarding this:

i wanted to find the the customers :-

who never recharge more than 5?

who recharge only once?

can anyone please help me in this?

Thank you.

 

 

 

 

 

1 Solution

Accepted Solutions
Andrei_Cusnir
Specialist
Specialist

Hello,

 

Perhaps this might help:

 

1. I have loaded a demo dataset:

 

2.Based on your requirement, you want to display:

  1. See Customer 2 and Customer 3 (Because their recharge doesn't exceed 5)
  2. See Customer 3 and Customer 4 (Because their recharge count, doesn't exceed 1)

3. You can create a Straight Table chart with CustomerID as dimension and for the RechargeAmount you can use the expression:

=IF(Aggr(NODISTINCT Sum(RechargeAmount), CustomerID)/Aggr(NODISTINCT Count(RechargeAmount), CustomerID) = 5, RechargeAmount, Null())

 

This expression counts the total sum of the RechargeAmount per customer and divide based on the total count of the RechargeAmount per customer. If the response is 5, then it means that all the RechargeAmounts were 5, otherwise if the response is a higher number, it means that at least on of the RechargeAmount values is higher than 5.

 

The outcome is:

 

4. You can also create a table that will that CustomerID as dimension and another dimension with expression:

=If(Aggr(NODISTINCT Count(RechargeAmount), CustomerID) = 1, RechargeAmount, Null())

 

This expression, counts the total amount of RechargeAmount values per customer and if the response is 1 then the particular customer had only 1 recharge.

 

The outcome is:

 

NOTE: For both occasions you will have to also un-check the option "Include null values"

 

I hope that this information was helpful. In case I have misunderstood the use case scenario, please elaborate in details by providing additional information. However, if it has helped you resolve the issue, addressed your concerns or at least pointed you in the right direction, please mark it as Accepted Solution to give further visibility to other community members. 
 

Help users find answers! Don't forget to mark a solution that worked for you! 🙂

View solution in original post

2 Replies
Andrei_Cusnir
Specialist
Specialist

Hello,

 

Perhaps this might help:

 

1. I have loaded a demo dataset:

 

2.Based on your requirement, you want to display:

  1. See Customer 2 and Customer 3 (Because their recharge doesn't exceed 5)
  2. See Customer 3 and Customer 4 (Because their recharge count, doesn't exceed 1)

3. You can create a Straight Table chart with CustomerID as dimension and for the RechargeAmount you can use the expression:

=IF(Aggr(NODISTINCT Sum(RechargeAmount), CustomerID)/Aggr(NODISTINCT Count(RechargeAmount), CustomerID) = 5, RechargeAmount, Null())

 

This expression counts the total sum of the RechargeAmount per customer and divide based on the total count of the RechargeAmount per customer. If the response is 5, then it means that all the RechargeAmounts were 5, otherwise if the response is a higher number, it means that at least on of the RechargeAmount values is higher than 5.

 

The outcome is:

 

4. You can also create a table that will that CustomerID as dimension and another dimension with expression:

=If(Aggr(NODISTINCT Count(RechargeAmount), CustomerID) = 1, RechargeAmount, Null())

 

This expression, counts the total amount of RechargeAmount values per customer and if the response is 1 then the particular customer had only 1 recharge.

 

The outcome is:

 

NOTE: For both occasions you will have to also un-check the option "Include null values"

 

I hope that this information was helpful. In case I have misunderstood the use case scenario, please elaborate in details by providing additional information. However, if it has helped you resolve the issue, addressed your concerns or at least pointed you in the right direction, please mark it as Accepted Solution to give further visibility to other community members. 
 

Help users find answers! Don't forget to mark a solution that worked for you! 🙂
its_rajvir
Contributor III
Contributor III
Author

It works @Andrei_Cusnir.......Thankyouuuuuuu