Help on Chart/Table required

[#1]

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.

[MK_QSL]

=COUNT({<InvoiceYear = {'2014'}>}DISTINCT MemberCode)

-

COUNT({<InvoiceYear = {'<2014'}>}DISTINCT MemberCode)

[#3]

That's right for the label value. How about making it Dimension and Expression for the Chart?

[MK_QSL]

What Dimension you want?

[#5]

Dimension should be "Traded" and "Non-Traded" and expression should return count of members for each dimension.

[MK_QSL]

Your Non Traded I don't understood

Non-Traded: 5 (total non-traded) + 2 (not traded in >2013) = 7 members

[#7]

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.

[MK_QSL · Accepted solution]

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