Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Pivot table with set analysis

Hi everybody! I have some problem with such task:

we have a table with data:

Header 1Header 2Header 3Header 4
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:

Header 1Header 2Header 3Header 4
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

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!

15 Replies
Not applicable
Author

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

MK_QSL
MVP
MVP

Is it what you are looking for?

Not applicable
Author

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

Not applicable
Author

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,

MK_QSL
MVP
MVP

Still unclear for me.

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

Not applicable
Author

Ok, there is a small example

MK_QSL
MVP
MVP

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.

Not applicable
Author

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

Not applicable
Author

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