topic Pivot table with set analysis in QlikView

Pivot table with set analysis

Wed, 19 Feb 2014 07:35:25 GMT

Hi everybody! I have some problem with such task:

we have a table with data:

Header 1	Header 2	Header 3	Header 4
ClientID	ReceiptSum	ReceiptInterval	Date
11	101	100-200	01/01/2014
11	120	100-200	01/02/2014
11	50	0-100	02/02/2014
22	70	0-100	01/05/2014
22	150	100-200	10/01/2014

so the task is to build a pivot table like that:

Header 1	Header 2	Header 3	Header 4
ReceiptInterval	100-200	1	1
	0-100	2 (number of customers)	0
		1	2
		count of receipts

I tried several ways to build that table:

1)-in data model create table

Load

Count (ClientID) as Count_of_receipts,

ClientID ,

ReceiptInterval

resident TotalReceipts Group by ReceiptInterval, ClientID;

then use this table for building pivot tavle. It works but pivot table is insensitive to selections made by user.

2)- make table of several text objects, in each object i'll calculate certain value, for eaxmple: calculate number of customers, who have 1 receipt in the amount of 100-200, than calculate number of customers,who have 2 receipts in the amount of 100-200 etc. For such calculations I I've used set analysis, but it doesn't work.

=Count({$<ReceiptInterval={'100-200'}, count({<ReceiptInterval={'100-200)>}ReceiptInterval={1})>}distinct ClientID )

Can anybody help with this expression or another way to solve my problem? Thanks in advance!

Re: Pivot table with set analysis

Wed, 19 Feb 2014 08:34:39 GMT

Egor,

See the function Class() that may suit you:

Class(ReceiptSum, 100) as a calculated dimension

The count() function will be used as a normal expression

Fabrice

Re: Pivot table with set analysis

MK_QSL — Wed, 19 Feb 2014 09:10:49 GMT

Is it what you are looking for?

Re: Pivot table with set analysis

Wed, 19 Feb 2014 09:31:16 GMT

Manish, not exactly.

in your Pivot table:

dimensions:

-ReceiptIntervals

-ClientID

Expressions:

-number of client's receipts for each interval.

What I need:

dimensions:

-ReceiptIntervals (already done) (100-200,200-300)

-a certain number of receipts for period (1 , 2, 3, etc)

expression:

-number of clients, that have certain number of receipts on ReceiptInterval.

In my example the result is:

-2 clients have 1 receipt in 0-100 interval

-1 client has 2 receipts in 100-200 interval

-1 client has 1 receipt in 100-200 interval

-none has 2 receipts in 0-100 interval

Re: Pivot table with set analysis

Wed, 19 Feb 2014 09:39:28 GMT

Hi Egor

in Scripting You Can add like this

their two methods

If(ReceiptSum<100,'0-100','100-200')as Flag_if,

OR this can also be used in script

Class(ReceiptSum,100) as Flag_Class,

Re: Pivot table with set analysis

MK_QSL — Wed, 19 Feb 2014 09:46:58 GMT

Still unclear for me.

Can you upload your required result in Excel or table format?

Re: Re: Pivot table with set analysis

Wed, 19 Feb 2014 10:11:10 GMT

Ok, there is a small example

Re: Pivot table with set analysis

MK_QSL — Wed, 19 Feb 2014 10:20:12 GMT

I think you didn't get it properly.

We requested you to give us your required output in Excel or Table format, as we are unclear about your requirement.

Re: Pivot table with set analysis

Wed, 19 Feb 2014 10:29:02 GMT

hi Egor Canu Try follwing Script

TotalReceiptsRus:

LOAD sap_id,

store_id,

id & cash_id as key,

amount,

If(amount<100,'0-100',

If(amount<200,'100-200',

If(amount<300,'200-300',

If(amount<400,'300-400',

If(amount<500,'400-500',

If(amount<600,'500-600',

If(amount<700,'600-700',

If(amount<800,'700-800',

If(amount<900,'800-900',

If(amount<1000,'900-1000',)))))))))) AS Interval_Flag

Re: Re: Pivot table with set analysis

Wed, 19 Feb 2014 10:38:10 GMT

Ok, sorry. There is required output in Excel with explanation.

I don't have problem with Receipt intervals, they are already done in table. The main problem is in counting customers, that have certain number of receipts (1) in certain interval(100-200).

Re: Pivot table with set analysis

MK_QSL — Wed, 19 Feb 2014 10:53:03 GMT

Sorry but I am still unclear due to your format of excel file.

If you give us exact format then only can help you.

Re: Re: Re: Pivot table with set analysis

Wed, 19 Feb 2014 10:58:36 GMT

Re: Re: Re: Pivot table with set analysis

Wed, 19 Feb 2014 10:59:16 GMT

am also confused please have look at images

Re: Re: Pivot table with set analysis

Wed, 19 Feb 2014 11:07:29 GMT

Exact format is in Example.xlsx. this is how table should look like. in Example.qvw is my variant of this table, but it doesn't suit, because of insensitivity to current selection. Can you clearify what exactly is unclear?

Re: Re: Pivot table with set analysis

Wed, 19 Feb 2014 11:21:32 GMT

Ok, lets try to explain how I tried to solve the problem.

Table with data

Header 1	Header 2	Header 3	Header 4
ClientID	ReceiptSum	ReceiptInterval	Date
11	101	100-200	01/01/2014
11	120	100-200	01/02/2014
11	50	0-100	02/02/2014
22	70	0-100	01/05/2014
22	150	100-200	10/01/2014

I transformed to another table in script reload

Load

Count (ClientID) as Count_of_receipts,

ClientID ,

ReceiptInterval

resident TotalReceipts Group by ReceiptInterval, ClientID;

Result is next:

Calc_Table:

Header 1	Header 2	Header 3
Count_of_receipts	ClientID	ReceiptInterval
1	11	0-100
2	11	100-200
1	22	0-100
1	22	100-200

Then using this Calc_Table i build the pivot table

dimensions:

-count_of_receipts

-ReceiptInterval

expression: count(ClientID). as it should look like (see images and examples)

BUT. Because of calculation table in script I can't update pivot table after selections

Re: Re: Pivot table with set analysis

Fri, 21 Feb 2014 06:25:26 GMT

So, finally I built required table, but in very unusual way. Basically it is not a table,but 2dimension array of text objects. Every object contains expression like:

=Count ({$}If(Aggr(Count({$<ReceiptInterval={'100-200'}>}card_number),card_number)=1,card_number) )

Next object has the same expression, but with another ReceiptInterval and\or Count value:

=Count ({$}If(Aggr(Count({$<ReceiptInterval={'200-300'}>}card_number),card_number)=1,card_number) )

=Count ({$}If(Aggr(Count({$<ReceiptInterval={'200-300'}>}card_number),card_number)=2card_number) )

=Count ({$}If(Aggr(Count({$<ReceiptInterval={'200-300'}>}card_number),card_number)=3card_number) )

This "table" doesn't work very fast, but it is the only way i found...