0 Replies Latest reply: Aug 9, 2016 11:28 AM by alessandro ferrarese RSS

    Problem with alt states in set analysis

    alessandro ferrarese

      Hi, I have a simple database (simple but with milions of rows) like:


      ID (customer ID)StoreBehaviour


      the table key is ID+Store (meaning that a customer has only one possible behaviour in each store


      What I'm trying to achieve is: "how many € are spent in Store "B" with a specific behaviour (destination) by the clients who have a specific behaviour in Store "A" (origin)?",


      I have a table with this expression:


      sum({<[ID]=p({<[Store]=AltStore::[Store]>*<[Behaviour]=AltBehaviour::[Behaviour]>} [ID])>} €)


      Store and Behaviour are the dimensions of the table, and therefore represent the "destination"


      To cycle between the possible "origins", I have 2 selection tools, one for AltStore and one for AltBehaviour (and a macro to cycle all the combinations and export to excel)


      The problem is that the expression works fine when I make a selection in AltStore only, with no selection for AltBehaviour

      As I select AltBehaviour I have sometimes (not always) wrong results, with the sum of € for all the possibile origin behaviours higher than the results without selections on AltBehaviour

      Also the count(ID) doesn't work, with the same ratio


      As it doesn't happen always, I don't have a clue on the problem, can someone please tell me what's wrong in the above expression?


      many thanks... Alessandro