Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
Is it what you are looking for?
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
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,
Still unclear for me.
Can you upload your required result in Excel or table format?
Ok, there is a small example
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.
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
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).