Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
Can't build simple chart from the data as I am quite new to Qlik.
I've got transactions linked up against members and then joined members on member code. I need to create simple chart of members traded vs non-traded. This means there is no transaction information. Just a quick way to simulate I've done some inline sample data:
CommonData:
Load * Inline [
MemberCode, TransCount, TransactionId, InvoiceDate
'A100', 1, 1 , 1/1/2010
'A100', 1, 2 , 1/1/2014
'A101', 1, 3 , 1/1/2010
'A102', 1, 4 , 1/1/2014
'A103', 1, 5 , 1/1/2014
'A104', 1, 6 , 1/1/2014
'A105', 1, 7 , 1/1/2014
'A105', 1, 8 , 1/1/2014
'A105', 1, 9 , 1/1/2014
'A105', 1, 10 , 1/1/2014
'A105', 1, 11 , 1/1/2014
'A105', 1, 12 , 1/1/2014
'A106', 1, 13 , 1/1/2014
'A107', 1, 14 , 1/1/2014
'A108', 1, 15 , 1/1/2014
'A109', 1, 16 , 1/1/2014
'A110', 1, 17 , 1/1/2014
'A110', 1, 18 , 1/1/2014
'A110', 1, 19 , 1/1/2014
'A110', 1, 20 , 1/1/2010
];
Members:
Load * Inline [
MemberCode, Name
'A100', 'Member 1 Traded'
'A101', 'Member 2 Traded'
'A102', 'Member 3 Traded'
'A103', 'Member 4 Traded'
'A104', 'Member 5 Traded'
'A105', 'Member 6 Traded'
'A106', 'Member 7 Traded'
'A107', 'Member 8 Traded'
'A108', 'Member 9 Traded'
'A109', 'Member 10 Traded'
'A110', 'Member 11 Traded'
'A300', 'Member 12 Non-Traded'
'A301', 'Member 13 Non-Traded'
'A302', 'Member 14 Non-Traded'
'A303', 'Member 15 Non-Traded'
'A304', 'Member 16 Non-Traded'
];
From this data I should get two values with set analysis condition Year(Invoice date) >= 2013:
Traded: 11 (total traded)-2 (not traded in >2013) = 9 members
Non-Traded: 5 (total non-traded) + 2 (not traded in >2013) = 7 members
All this should show up in chart.
Thanks for any help.
Create a Straight Table
Dimension = Calculated Dimension
=ValueList('Traded','Non Traded')
Expression
=IF(ValueList('Traded','Non Traded') = 'Traded', COUNT({<InvoiceYear = {'2014'}>}DISTINCT MemberCode) -COUNT({<InvoiceYear = {'<2014'}>}DISTINCT MemberCode),
COUNT({<MemberCode = {"=COUNT(TransCount)=0"}>}MemberCode) + COUNT({<InvoiceYear = {'<2014'}, MemberCode = E({<InvoiceYear = {'2014'}>})>}DISTINCT MemberCode))
=COUNT({<InvoiceYear = {'2014'}>}DISTINCT MemberCode)
-
COUNT({<InvoiceYear = {'<2014'}>}DISTINCT MemberCode)
That's right for the label value. How about making it Dimension and Expression for the Chart?
What Dimension you want?
Dimension should be "Traded" and "Non-Traded" and expression should return count of members for each dimension.
Your Non Traded I don't understood
Non-Traded: 5 (total non-traded) + 2 (not traded in >2013) = 7 members
One figure is wrong, should 1 instead 2 based on example.
Non traded ones are that have no transaction linked up. In my example that would be:
'A300', 'Member 12 Non-Traded'
'A301', 'Member 13 Non-Traded'
'A302', 'Member 14 Non-Traded'
'A303', 'Member 15 Non-Traded'
'A304', 'Member 16 Non-Traded'
That's 5.
From set Analysis also should add up: line 4 (A100) but line 5 matched on invoice date >=2013 so should ignore, same for A110 as has invoices in 2010 and 2014 - ignore it, so left only one: line 5 (A101).
Now you get 5+1 = 6 non traded members.
Microsoft SQL query would look like this:
SELECT COUNT(*) as Total_Traded_MemberCount From Members
UNION ALL
SELECT COUNT(*) as Total_NON_Traded_MemberCount FROM Members Where MemberCode NOT IN (Select MemberCode From Transactions Where InvoiceDate >= '2013-01-01')
I hope this helps.
Create a Straight Table
Dimension = Calculated Dimension
=ValueList('Traded','Non Traded')
Expression
=IF(ValueList('Traded','Non Traded') = 'Traded', COUNT({<InvoiceYear = {'2014'}>}DISTINCT MemberCode) -COUNT({<InvoiceYear = {'<2014'}>}DISTINCT MemberCode),
COUNT({<MemberCode = {"=COUNT(TransCount)=0"}>}MemberCode) + COUNT({<InvoiceYear = {'<2014'}, MemberCode = E({<InvoiceYear = {'2014'}>})>}DISTINCT MemberCode))