Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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.
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
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'))
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)
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?
The data is too large to put in an inline statement.