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

    Pivot table with set analysis

    egor yadchenko

      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!