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: 
Anonymous
Not applicable

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

10 Replies
zhadrakas
Specialist II
Specialist II

Hello Teresa,

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

Expresssions are the sum(Flag_Sold1UniqueItem) ..

Source_Data:
LOAD * INLINE [
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
Load Year,
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

Anonymous
Not applicable
Author

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

zhadrakas
Specialist II
Specialist II

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

Anonymous
Not applicable
Author

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.

zhadrakas
Specialist II
Specialist II

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

Anonymous
Not applicable
Author

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')) 

zhadrakas
Specialist II
Specialist II

again use this part in your load script:

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)

Anonymous
Not applicable
Author

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?

Anonymous
Not applicable
Author

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