topic Re: Help with Set Analysis in QlikView

Help with Set Analysis

sangeess21 — Wed, 25 Nov 2020 16:16:04 GMT

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,

		Year	2015	2016	2017
Direct/Indirect Customer	Service Class	SP Name	SP Value	SP Value	SP Value
Direct	ABC	Customer A	$0.00	$0.00	$2000.00
Direct	ABC	Customer B	$0.00	$0.00	$4000.00
Direct	XYZ	Customer B	$0.00	$0.00	$6000.00
Direct	XYZ	Customer A	$250.00	$350.00	$0.00
Direct	Total		$250.00	$350.00	$12,000.00
Indirect	XYZ	Customer A	$0.00	$0.00	$3,500.00
Indirect	Total	Customer B	-	-	$3,500.00

I would like to get a pivot table like this.

Direct/Indirect Customer	Service Class	SP Name	SP Value
Direct	ABC	Customer B	$4000.00
Direct	XYZ	Customer 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 Customer	Service Class	SP Name	SP Value
Direct	ABC	Customer B	$4000.00
Direct	ABC	Customer A	$2000.00
Direct	XYZ	Customer B	$6000.00
Indirect	XYZ	Customer 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!

Re: Help with Set Analysis

Anil_Babu_Samineni — Thu, 19 Oct 2017 15:34:38 GMT

Would you like to do Cross Table?

Re: Help with Set Analysis

sunny_talwar — Thu, 19 Oct 2017 15:47:30 GMT

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

Re: Help with Set Analysis

andrei_delta — Thu, 19 Oct 2017 20:18:41 GMT

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

Re: Help with Set Analysis

sangeess21 — Fri, 20 Oct 2017 14:24:45 GMT

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

Re: Help with Set Analysis

sangeess21 — Fri, 20 Oct 2017 14:28:42 GMT

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.

Re: Help with Set Analysis

sunny_talwar — Fri, 20 Oct 2017 15:02:57 GMT

See if this addresses your confidentiality concerns

Preparing examples for Upload - Reduction and Data Scrambling

Re: Help with Set Analysis

sangeess21 — Fri, 20 Oct 2017 15:29:54 GMT

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

Re: Help with Set Analysis

sunny_talwar — Fri, 20 Oct 2017 15:36:20 GMT

Try this

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

Re: Help with Set Analysis

sangeess21 — Fri, 20 Oct 2017 18:13:37 GMT

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.

Re: Help with Set Analysis

sunny_talwar — Fri, 20 Oct 2017 18:19:09 GMT

What is the expected output here? This?

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

Re: Help with Set Analysis

sangeess21 — Fri, 20 Oct 2017 18:27:55 GMT

The above expression returns only Direct. I would like to return new customers in Indirect too. The result should be like

Direct/Indirect Customer	Service Class	Customer	SP Value
Direct	Service A	Customer B	$638.19
Direct	Service A	Customer E	$29,804.20
Direct	Service B	Customer B	$2,400.18
Direct	Service B	Customer D	$605.00
Direct	Service B	Customer E	$5439.27
Indirect	Service A	customer F	$117.00
Indirect	Service C	Customer F	$85.14

Thanks!

Re: Help with Set Analysis

sunny_talwar — Fri, 20 Oct 2017 18:41:02 GMT

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)

Re: Help with Set Analysis

sangeess21 — Fri, 20 Oct 2017 19:07:03 GMT

Thank you, Sunny It working now. Can you please explain what you are doing here? Why is ID added in the Customer set analysis?

Re: Help with Set Analysis

sunny_talwar — Sat, 21 Oct 2017 20:03:33 GMT

So, the expression is showing the sum of sales for all customers who have no sales in last years within the ID 700000, 700001, and 700002 and show there sales in this year.

Why is ID added in the Customer set analysis?

Have no idea why you had them in the first place? I just tried to use your expression and expand on it

Re: Help with Set Analysis

sangeess21 — Mon, 23 Oct 2017 15:11:02 GMT

Thank you, Sunny. I was trying to filter by ID for Direct and Indirect customers in my expression. I have one more question. Though we are filtering customers who has no sales in last years in the expression, why does Customer G show up in the result when we select 'Service A' Service Class?

Thanks!

Re: Help with Set Analysis

sunny_talwar — Mon, 23 Oct 2017 15:16:42 GMT

It sort of depends on what your final expectations are with regards to this expression, but you can play around with it to fix it... for instance the issue that you pointed out can be resolved by making this change

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

Re: Help with Set Analysis

sangeess21 — Mon, 23 Oct 2017 15:49:01 GMT

Ok. Thank you so much