10 Replies Latest reply: Jan 26, 2017 12:46 AM by Aiolos Zhao

Help getting a count of unique items sold by year

I have data like the below in which i want to create a chart that will show me how many distinct items sold per customer name.  So in the example below, Customer ABC sold 2 distinct items.  Once that is done, i want to show how many customers sold 1 unique item, how many customers sold 2 unique items and how many customers sold 3 or more unique items.

How would I accomplish this.

Year     Customer Name Item sold
2014      abc           AA
2014      abc           BB
2014      abc           BB

2014      def           AA

2014      ghi           CC
2014      ghi           AA
2014      ghi           BB

2014   jkl  AA

2014   mno  BB
2014   mno  CC
2014   mno  AA

Customers who sold 1 unique item = 2
Customers who sold 2 unique items = 1
Customers who sold 3 or more unique items = 2

• Re: Help getting a count of unique items sold by year

Hello Teresa,

You can use this script. The create a Chart with Dimensions Year and Customer.

Expresssions are the sum(Flag_Sold1UniqueItem) ..

Source_Data:
Year, CustomerName, ItemSold
2014, abc, AA
2014, abc, BB
2014, abc, BB
2014, def, AA
2014, ghi, CC
2014, ghi, AA
2014, ghi, BB
2014, jkl, AA
2014, mno, BB
2014, mno, CC
2014, mno, AA
]
;

//Customers who sold 1 unique item
CustomerName,
if(count(DISTINCT ItemSold)=1,1,0) as Flag_Sold1UniqueItem,
if(count(DISTINCT ItemSold)=2,1,0) as Flag_Sold2UniqueItems,
if(count(DISTINCT ItemSold)>2,1,0) as [Flag_Sold>2UniqueItems]
Resident Source_Data
group by Year, CustomerName
;

drop table Source_Data;

regards

tim

• Re: Help getting a count of unique items sold by year

The above was just a sample of the data.  I don't think I can do this.  My first step, which worked was to Count

Count(DISTINCT(Item Sold)) , then I am trying to try this:

IF(MATCH(Class(Count(Distinct(Item Sold),’1’,’1 Item Sold’), Class(Count(Distinct(Item Sold),’1’,’1Items Sold’),’3 or more Item’s Sold

So far I have not been able to get this to work

• Re: Help getting a count of unique items sold by year

if you want to have this in one field you can do

if(Count(DISTINCT([Item Sold]))=1, '1 Item Sold',
if( Count(DISTINCT([Item Sold]))=2, '2 Items Sold',
'3 or more Items Sold'))
as YourFieldName

• Re: Help getting a count of unique items sold by year

that kindof worked.  I do a row for every customer as to how many items they sold.  Now I need to put this in a chart so want to could how many customers sold only 1 item, how many customers only sold 2 items etc.  similar to this.  the gray would be the customers that sold 1 item, black is the customers that sold 2 items and the red are the customers that sold 3 or more.

• Re: Help getting a count of unique items sold by year

whats the problem at this point? your expressions should look like this:

sum(if(YourFieldName='1 Item Sold',1))

this should give you the expected result

• Re: Help getting a count of unique items sold by year

I want to get this:

total number of rows that = 1 item

total number of rows that = 2 items

so I would get:

Total of 5 Submission Names that sold just 1 item

Total of 1 Submission Name that sold 2 items

My expression is this:

if(Count(DISTINCT([Items]))=1, '1 Item',
if( Count(DISTINCT(
[Items]))=2, '2 Items',
'3 or more Items'))

• Re: Help getting a count of unique items sold by year

if(Count(DISTINCT([Items]))=1, '1 Item',
if( Count(DISTINCT(
[Items]))=2, '2 Items',
'3 or more Items'))  as Flag_tems_Sold

Then use this Expressions in your Chart:

sum(if(Flag_tems_Sold='1 Item',1)

sum(if(Flag_tems_Sold='2 Items',1)

sum(if(Flag_tems_Sold='3 or more Items',1)

• Re: Help getting a count of unique items sold by year

thank you.  I will try that.  I am still fairly new to Qlik so I would ask if this would be done with a Load Inline?

• Re: Help getting a count of unique items sold by year

The data is too large to put in an inline statement.

• Re: Help getting a count of unique items sold by year

Hi Teresa,

If you want to make it in the front-end, you can do it like this:

But if your data is too large, the aggr() function maybe takes too long time to calculate it, you can try it.

Thanks.

Aiolos Zhao