15 Replies Latest reply: Feb 21, 2014 1:25 AM by egor yadchenko

Pivot table with set analysis

Hi everybody! I have some problem with such task:

we have a table with data:

ClientIDReceiptSumReceiptIntervalDate
11101100-20001/01/2014
11120100-20001/02/2014
11500-10002/02/2014
22700-10001/05/2014
22150100-20010/01/2014

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

ReceiptInterval

100-20011
0-1002 (number of customers)0
12
count of receipts

I tried several ways to build that table:

1)-in data model create table

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

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

Is it what you are looking for?

• Re: Pivot table with set analysis

Manish, not exactly.

dimensions:

-ReceiptIntervals

-ClientID

Expressions:

-number of client's receipts for each interval.

What I need:

dimensions:

-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

Still unclear for me.

• Re: Pivot table with set analysis

Hi Egor

in Scripting You Can add like this

their two methods

1)

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

2)

OR this can also be used in script

Class(ReceiptSum,100) as Flag_Class,

• Re: Re: Pivot table with set analysis

Ok, there is a small example

• Re: Pivot table with set analysis

hi Egor Canu Try follwing Script

TotalReceiptsRus:

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: Pivot table with set analysis

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: Re: Pivot table with set analysis

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

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

• Re: Re: Pivot table with set analysis

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: Re: Pivot table with set analysis

am also confused please have look at images

• Re: Re: Pivot table with set analysis

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

Table with data

ClientIDReceiptSumReceiptIntervalDate
11101100-20001/01/2014
11120100-20001/02/2014
11500-10002/02/2014
22700-10001/05/2014
22150100-20010/01/2014

I transformed to another table in script reload

Count (ClientID) as Count_of_receipts,

ClientID ,

ReceiptInterval

resident TotalReceipts Group by ReceiptInterval, ClientID;

Result is next:

Calc_Table:

Count_of_receiptsClientIDReceiptInterval
1110-100
211100-200
1220-100
122100-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

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