topic Re: Re: Help on Chart/Table required in QlikView

Help on Chart/Table required

Mon, 10 Nov 2014 16:31:43 GMT

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.

Re: Help on Chart/Table required

MK_QSL — Mon, 10 Nov 2014 16:40:24 GMT

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

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

Re: Help on Chart/Table required

Mon, 10 Nov 2014 16:42:59 GMT

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

Re: Help on Chart/Table required

MK_QSL — Mon, 10 Nov 2014 16:52:01 GMT

What Dimension you want?

Re: Help on Chart/Table required

Mon, 10 Nov 2014 16:54:01 GMT

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

Re: Help on Chart/Table required

MK_QSL — Mon, 10 Nov 2014 16:58:45 GMT

Your Non Traded I don't understood

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

Re: Re: Help on Chart/Table required

Mon, 10 Nov 2014 17:09:27 GMT

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.

Re: Help on Chart/Table required

MK_QSL — Mon, 10 Nov 2014 17:18:40 GMT

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