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