Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sangeess21
Creator
Creator

Help with Set Analysis

Hi,

I would like to show new customers in a pivot table. A new customer is one who has not purchased anything(Direct/Indirect) from the company in the past. The concept I use is, if the customer does not have any SP value in the past and does have a SP Value this year, then that customer is a new customer.

If the below example is considered,

Year201520162017
Direct/Indirect CustomerService ClassSP NameSP ValueSP ValueSP Value
DirectABCCustomer A$0.00$0.00$2000.00
DirectABCCustomer B$0.00$0.00$4000.00
DirectXYZCustomer B$0.00$0.00$6000.00
DirectXYZCustomer A$250.00$350.00$0.00
DirectTotal$250.00$350.00$12,000.00
IndirectXYZCustomer A$0.00$0.00$3,500.00
IndirectTotalCustomer B--$3,500.00

I would like to get a pivot table like this.

Direct/Indirect CustomerService ClassSP NameSP Value
DirectABCCustomer B$4000.00
DirectXYZCustomer B$6000.00

But when use the below set analysis,

if(( if ([Direct/Indirect Customer] = 'Direct', sum({<ID={'1','2','3'},Year={"$(=year(today(1)))"}>}Amount), sum({<ID-={'1','2','3'},Year={"$(=year(today(1)))"}>}Amount)) <>0 and

if ([Direct/Indirect Customer] = 'Direct', sum({<ID={'1','2','3'},Year-={"$(=year(today(1)))"}>}Amount), sum({<ID-={'1','2','3'},Year-={"$(=year(today(1)))"}>}Amount)) = 0,

if ([Direct/Indirect Customer]='Direct', sum({<ID={'1','2','3'},Year={"$(=year(today(1)))"}>}Amount), sum({<ID-={'1','2','3'},Year={"$(=year(today(1)))"}>}Amount)))

I get this result where Customer A is not a new customer as it has purchased directly from the company in 2015 and 2016.

Direct/Indirect CustomerService ClassSP NameSP Value
DirectABCCustomer B$4000.00
DirectABCCustomer A$2000.00
DirectXYZCustomer B$6000.00
IndirectXYZCustomer A$3500.00


If I use the below expression, I do not get any result.

if((if([Direct/Indirect Customer] = 'Direct',sum({<ID={'1','2','3'},Year={"$(=year(today(1)))"}>}Amount),sum({<ID-={'1','2','3'},Year={"$(=year(today(1)))"}>}Amount))<>0 and

if([Direct/Indirect Customer] = 'Direct',sum({<ID={'1','2','3'},Year-={"$(=year(today(1)))"}>}Amount))=0 and

if([Direct/Indirect Customer] = 'Indirect',sum({<ID-={'1','2','3'},Year-={"$(=year(today(1)))"}>}Amount))=0,

if([Direct/Indirect Customer]='Direct', sum({<ID={'1','2','3'},Year={"$(=year(today(1)))"}>}Amount),sum({<ID-={'1','2','3'},Year={"$(=year(today(1)))"}>}Amount)))

Am I missing anything here? Any help is greatly appreciated!

Thanks!

1 Solution

Accepted Solutions
sunny_talwar

Indirect doesn't have ID 700000, 700001 or 700002... that is why they did not show up... may be try this

Sum({<Year = {$(=Year(Today(1)))}, Customer -= {"=Sum({<ID = {'700000','700001','700002'}, Year -= {$(=Year(Today(1)))}>}Value) > 0"}>}Value)


Capture.PNG

View solution in original post

17 Replies
Anil_Babu_Samineni

Would you like to do Cross Table?

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
sunny_talwar

What is ID field here? Would you be able to share a sample?

andrei_delta
Partner - Creator III
Partner - Creator III

Hello,

Just an observation when you are using set analysis to calculate values, be sure to take the nulls out of that field (the one with the values) otherwise it will mess the result...

Regards,

Andrei

sangeess21
Creator
Creator
Author

No. The users would like to see this as a pivot table so they can keep adding dimensions when they need.

sangeess21
Creator
Creator
Author

The ID is the ID of the customer. If the ID is 1,2 or 3 then they are Direct Customers else Indirect Customers. I would love to share a sample, but it has sensitive information in it.

sunny_talwar

See if this addresses your confidentiality concerns

Preparing examples for Upload - Reduction and Data Scrambling

sangeess21
Creator
Creator
Author

I created a sample of the example I posted above. Hope this helps. Thanks!

sunny_talwar

Try this

Sum({<ID = {'1','2'}, Year = {$(=Year(Today(1)))}, Customer = {"=Sum({<ID = {'1','2'}, Year -= {$(=Year(Today(1)))}>}Value) = 0"}>}Value)


Capture.PNG

sangeess21
Creator
Creator
Author

Thanks Sunny. This works for the above scenario, but not mine. I've attached the sample where the above set analysis doesn't work. I would like only Customer B, D, E and F to pop up as new customers as they do not have any value in the past.