Qlik Community

QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
tkjenkin
New Contributor

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
Valued Contributor

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:
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

tkjenkin
New Contributor

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

zhadrakas
Valued Contributor

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

tkjenkin
New Contributor

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.

zhadrakas
Valued Contributor

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

tkjenkin
New Contributor

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

zhadrakas
Valued Contributor

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

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)

tkjenkin
New Contributor

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?

tkjenkin
New Contributor

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

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

Community Browser