Problem with alt states in set analysis

alferrar — Tue, 09 Aug 2016 15:28:41 GMT

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

topic Problem with alt states in set analysis in QlikView

Problem with alt states in set analysis