topic Re: Help on set expression with Different Scenarios . in QlikView

Help on set expression with Different Scenarios .

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

Hi All ,

Need help on set expression with following scenarios .

1) How to Find Number of Customers who Did not Purchase any Item in Year 2015 ?

2) How to Find Number of Customers who Did not Purchase any Item in Year 2015 But have Done Purchase in Year 2016 ?

3) How to Find Customer who Purchased More Number of Items ?

4) How to Find Customer who Purchased More TYPES of Items ?

5) How to Find Number of Customers who have Purchased more than one type of Item in Year 2015 ?

6) How to show item least purchased ?

Also Find sample Attached .

Thanks & Regards

Shekar

Re: Help on set expression with Different Scenarios .

shekhar_analyti — Thu, 20 Apr 2017 09:22:59 GMT

Please help stalwar1‌

Re: Help on set expression with Different Scenarios .

sunny_talwar — Thu, 20 Apr 2017 10:59:24 GMT

Look here at vinieme12‌ and my responses

Nested Set Analysis - Customer bought in both time periods

Specifically for your sample, it might be helpful for us to know what are the expected output for all of the question listed above...

or if someone else have free time... they might be able to give it a try

Re: Help on set expression with Different Scenarios .

shekhar_analyti — Thu, 20 Apr 2017 11:41:43 GMT

Thanks for reply .. I thought you have taken sabbatical or something ...

Re: Help on set expression with Different Scenarios .

sunny_talwar — Thu, 20 Apr 2017 11:44:14 GMT

Will be available on and off

Re: Help on set expression with Different Scenarios .

shekhar_analyti — Thu, 20 Apr 2017 11:47:09 GMT

I got most of it .. I request if you can help me on this .

How to Find Number of Customers who Did not Purchase any Item in Year 2015 ?

Re: Help on set expression with Different Scenarios .

vinieme12 — Thu, 20 Apr 2017 11:56:01 GMT

like below

count({<Customer = {"=Count({<Year={2016}>} Item)=0"}>}Distinct Customer)

Re: Help on set expression with Different Scenarios .

sunny_talwar — Thu, 20 Apr 2017 12:15:43 GMT

May be this if you don't have year field.... but recommed creating year field in the script.....

=Count(DISTINCT {<Customer = {"=Count({<Date = {""=Year(Only({<Date>} Date)) = 2015""}>}Item) = 0"}, Date>} Customer)

Re: Help on set expression with Different Scenarios .

shekhar_analyti — Sun, 23 Apr 2017 10:16:52 GMT

I am getting error for following
Number of Customers who Did not Purchase any Item in Year 2015 But have Done Purchase in Year 2016

Re: Help on set expression with Different Scenarios .

shekhar_analyti — Sun, 23 Apr 2017 10:24:14 GMT

vinieme12‌

Re: Help on set expression with Different Scenarios .

vinieme12 — Sun, 23 Apr 2017 10:28:30 GMT

Can't open the App ,using PE

What is the expression you are using?

In your sample data all customers have purchases in both 2015 and 2016, so i would expect 0 results

Re: Help on set expression with Different Scenarios .

vinieme12 — Sun, 23 Apr 2017 10:33:35 GMT

I've manually removed 2015 sales for two people in the dataset

Load Script:

LOAD Customer,

Date as DateIS,

Year(Date) as YearIS,

Item

FROM

(ooxml, embedded labels, table is Sheet1);

Expression

=count({< Customer ={"=min(TOTAL <Customer> YearIS)=2016"} >} distinct Customer)

Result

Re: Help on set expression with Different Scenarios .

shekhar_analyti — Sun, 23 Apr 2017 10:35:54 GMT

Updated excel

=count({<Customer= p({"=Count({<Year={2016}>} Item)<>0"}) - p({"=Count({<Year={2015}>} Item)=0"})>}Distinct Customer)

Re: Help on set expression with Different Scenarios .

shekhar_analyti — Sun, 23 Apr 2017 10:37:57 GMT

set expression with p and e in needed ... i have goofed up somewhere ..

Re: Help on set expression with Different Scenarios .

vinieme12 — Sun, 23 Apr 2017 10:39:51 GMT

even in this excel there are no customers that satisfy the condition of having the first purchase in 2016,

Can you pick one customer as example?

Re: Help on set expression with Different Scenarios .

shekhar_analyti — Sun, 23 Apr 2017 10:46:09 GMT

Ann Baker did not purchase anything in 2015 , but in 2016 she purchase refill ..

Re: Help on set expression with Different Scenarios .

vinieme12 — Sun, 23 Apr 2017 10:50:48 GMT

See attached, removed few rows for other people as example

Re: Help on set expression with Different Scenarios .

shekhar_analyti — Sun, 23 Apr 2017 11:41:11 GMT

Thanks for reply ...

It seems i led to confusion here . Apologies .

Actually i was looking for expression which would get me records highlighted in yellow .

For Janet in 2015 Item is null but in 2016 it has lamp .. I was looking for such records .

Thanks & Regards

Shekar

Re: Help on set expression with Different Scenarios .

vinieme12 — Sun, 23 Apr 2017 11:58:12 GMT

Try below Customers with null items in 2015 but with possible items in 2016

=count({< Customer = {"=count({<YearIS={2015}>} Item)=0"}> * <Customer = {"=count({<YearIS={2016}>} Item)>0"}>}distinct Customer)

Re: Help on set expression with Different Scenarios .

sunny_talwar — Sun, 23 Apr 2017 23:09:56 GMT

In a single set analysis... may be like this

=Count(DISTINCT {< Customer = {"=Count({<YearIS = {2015}>} Item) = 0 and Count({<YearIS = {2016}>} Item)>0"}>} Customer)