Query on a macro

[#1]

Hi all,

I'm trying to solve a problem i have but i'm a bit stuck. I'll try to resume my situation, and what i'm trying to do.

I have some pivot tables that show information about some clients sales. There is a lot of filters, but what really matters here is the date filter. There, you can select some months, and obviusly sales will just show sales information about the clients who sold something on that period. Now here is the problem, what i need is that the pivot tables just show the sales information of the clients who sold something in EVERY month of the period. For example if you select from january'09 to april'09, it must show just the information for the clients who sold in january AND february AND march AND april.

The date filter isn't a primary key, so what i do to filter the pivot tables is to check that the sale's date operation is in the period of months. I can't make the field date filter a key linked to the sales field date because of other tables.

I've tried to create tables with the clients and the months they sold something, and added to the pivot table expressions a condition to count() the fiield months of that table to check if its equal to the months selected, but this doesn't work, basically because QV doesn't let me to make that count in the expression without adding TOTAL.

Desperate about not finding a solution, i'm trying now another option. I've made a macro that whenever the date filter is modified, it gets all the selected values of the field clients, and add them to a string, that in the end is assigned to a variable. The variable will end with a value, for example, like this: "client1";"client2";... Then i've added a condition on the pivot table expressions to check if the client is in that list (with this list i mean the long string saved on the variable). Of course just this don't solve the problem, is just a first approach. My final intention is that the list contains the clients who has sales in all the months, so my next step is to modify the macro so i can make some kind of query to count the diferent months where the client has sales and check if that number is equal to the months period selected. So basically i need to make a query, over a resident table, in the macro, is that possible?

After all this long and bored monologue, i've must thanks even just for read it because i need is tough, and i will really appreciate any piece of help you can give me, even another solution to the problem, because i thing i'm starting to not think clear and my ways to try to solve the problem are starting to be a bit fuzzy.

Thaks beforehand,

Jacobo

[#2]

Would you please attach a file with what you have so far.

Thank you.

[#3 · Accepted solution]

My attempt at the solution is attached.

The basic trick is two things:

1. Don't actually select on the Date field because that will immediately filter to the start and end month, instead use variables.

2. Use concat to create two lists of month (date) values that can be compared between a given customer and the filtering month set.

[#4]

Hi Eugene,

Thanks for your time in my search for a solution. I've been trying the file you attached me, and it works fine when the table has the client as the customer, but i've been trying using instead the Misc field (i renamed it to Product and added some lines) as dimension and isn't work in that situation. I'll think about some way to solve de problem based on your example. I attach your file with my table TEST so you can see what table i need to work in case you know another way to solve it.

Lots of thanks!

[#5]

You are very welcome!

A solution to the second issue is attached. The cause was that you had Product as a dimension, but product purchases were unique by month. Thus we needed to ignore product (and any other dimension you add) when calculating which months had ANY transaction for a given customer. This was accomplished by using the "total <Customer>" clause which will cause the expression to ignore all dimensions but customer.