Hi, I have a simple database (simple but with milions of rows) like:
| ID (customer ID) | Store | Behaviour | € |
|---|
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
